SQL Queries Practice Question

                                                                                 DBMS , IOE, BCA ,CSIT

SQL COMMANDS PRACTICE QUESTIONS                                                            

 

Structured Query Language is a standard Database language that is used to create, maintain, and retrieve the relational database. 

 

Let us consider a STUDENT Table 

 

STUDENT

 

Write a SQL command to : 

 

1. Retrieve attributes ROLL_NO and NAME of all students

 

SELECT roll, name FROM STUDENT;

 

2. Retrieve ROLL_NO and NAME of the students whose ROLL_NO is greater than 1

 

SELECT roll, name FROM STUDENT where roll> 1;

 

3. Retrieve all attributes of students whose ROLL_NO is greater than 1

 

SELECT * FROM STUDENT where roll> 1;

 

4. Represent the relation in ascending order by AGE

 

SELECT * FROM STUDENT ORDER BY age;

 

5. Retrieve distinct values of an address 

 

SELECT DISTINCT address FROM STUDENT.

 

 

AGGREGATE FUNCTIONS

 

Aggregation functions are used to perform mathematical operations on data values of a relation.

 

 

6. Count the number of Address in the above table 

 

SELECT COUNT (Address) FROM STUDENT.

 

7. Find the sum of all ages from the above table .

 

SELECT SUM(age) from STUDENT.

 

Similarly use MAX, MIN and AVG functions.

 

8. Find the sum of ages of students who belong to the same City.

 

SELECT Address, SUM(age) FROM STUDENT GROUP BY (address).

 

HAVING AND WHERE CLAUSE 

(where clause cannot be used with aggregates, but the having clause can.

The where clause works on row’s data, not on aggregated data. )

9. Find the Roll and Name of student whose age >18

 

SELECT roll, name FROM STUDENT WHERE age>18.

 

10. Find Address and sum of ages of Students from the same City and finally find those whose sum of ages  > 30.

SELECT Address, SUM(age) AS total FROM STUDENT GROUP BY address HAVING total >18.

 

 

JOINS

 

 

Student

 

 

Course Table

 

 

INNER JOIN 

 

SELECT Course.CID,Student.Name FROM Student INNER JOIN Course ON Course.EnrollNo = Student.EnrollNo ORDER BY Course.CourseID;

 

 

Inner Join is basically performed by just selecting the records having the common values or the matching values in both tables. In case of no common values, no data is shown in the output.

 

OUTER JOIN 

 

LEFT OUTER JOIN 

 

SELECT Student.Name,Course.CID FROM Student LEFT OUTER JOIN Course ON Course.EnrollNo = Student.EnrollNo ORDER BY Course.CourseID;

 

Output

 

 

RIGHT OUTER JOIN 

 

Record

 

Course

 

SELECT Record.NAME,Course.CID FROM Record RIGHT JOIN Course ON Course.Roll = Record.Roll;

 

Output

 

 

Full Join 

 

SELECT Record.NAME,Course.CID FROM Record FULL JOIN Course ON Course.Roll = Record.Roll ;

 

SUBQUERIES

DATABASE

 

 

STUDENT

 

11. Display NAME, ADDRESS, PHONE of the students from DATABASE table whose section is A

SELECT NAME, LOCATION, PHONE FROM DATABASE WHERE ROLL IN (SELECT ROLL_NO FROM STUDENT WHERE SECTION='A');