How to get information from a database

After designing a database in MSSQL Server or MySQL, how do you retrieve specific information from the database? You need to know SQL (Structured Query Language) and this is what will help you retrieve information. Read on for highlights.

First of all, you need to know what a schema and an instance means for database tables.

A schema includes all the fields in the table, while instance means collecting data at a specific time.

For example, you have created the following tables for a small university database. (I only give you the tables here.)

student:

matNr |SNAME

professor:

pname| psalary

class:

classNr| room | day | pname

take:

matNr | classNr |class

TOE:

matNr | classNr |h|tasalary

The greasy field (s) in a table represents the primary key of the table that uniquely identifies entities (tables) in an entity set (a set of similar tables).

Now, several queries to the database and SQL statements for these queries could be:

1) List of all students at the university.

SELECT name from Student

Here, SELECT is a SQL keyword that is aimed at displaying the names of students at the university from the Student Table. FROM is also a SQL keyword.

2) List of students whose grade was ‘A’ in CSE 303.

SELECT sName FROM Student, TAKE WHERE Student.matNr = Taker.matNr AND classNr = ‘CSE 303’ AND grade = ‘A’

Here, SELECT aims to show the students’ names from a pairing of students and takes tables as the class No or course is’ CSE 303 ‘and the student’s grade is’ A’.

3) Show the names of TAs whose salary is over $ 1500.

SELECT sName from Student, TAKE WHERE Student.matNr = TA.matNr AND Tasalary> 1500

4) Which professor is teaching CSE 303?

SELECT pName FROM Class, Professor WHERE Class.pName = Professor.pName AND classNr = ‘CSE 303’

5) List of students in the CSE department.

SELECT sName from Student, TAKING WHERE Student.matNr = Taking.matNr AND classNr = ‘CSE%’

Here, the request asks to list students in the CSE department. But in our tables we have no field for department. So we find a resort by a pairing of Student and take tables and type classNr = ‘CSE%’, which means the courses beginning with the characters ‘CSE’.

In summary, here are a few examples of how to use SQL query languages ​​to extract your desired information from a particular database, in my case a small university database. You can find other query languages ​​such as relational algebra to extract information from databases. But SQL is more user friendly, widespread and popular.