Structure Query Language

Some questions on Topic

IMPORTANT TERMS & DEFINITIONS

1. SQL. Structure Query Language is the language used by most relational  database systems. 

2. DDL. DDL is the part of SQL. It stands for data definition language. It provides statements for creation and deletion of the database.

3. DML. DML is also the part of the SQL. It stands for data manipulation language. It provides statements for manipulating the database. It includes commands to insert, delete and modify tuples in the database.

4. Constraints. SQL provides many constraints, which are used to enforce rules at table creation level whenever row is inserted, updated or deleted from the table. For example, NOT NULL, PRIMARY KEY, CHECK, etc.

5. CREATE TABLE. This statement is used for creating the new database.

6. ALTER TABLE. This command is used to change the table. We can add new column, change the data type of column or drop any constraint using alter command.

7. DROP. We can use Drop constraint from a table.

8. DROP TABLE. This command is used to remove the table physically.

9. INSERT INTO. This command adds a new record to the end of an existing database.

10. DELETE. This command is used to delete rows of a table.

11. UPDATE. This command is used to change the value in the table.

12. SELECT. This command is used to retrieve information from one or more database.

13. SELECT INTO. This command is used to create the new table using the existing one.

14. Aggregate functions. The aggregate functions are group functions. They return result base on groups of rows, rather than one result per rows as returned by single row function.

15. CREATE VIEW. A view is table that has no data of its own. A view is derived from another table. You can create view using CREATE VIEW command.

PREVIOUS YEAR'S QUESTIONS [1 and 2 Marks]

1. Write the SQL for (a) to (f) and write the outputs for (g) on the basis of the table HOSPITAL :

TABLE : HOSPITAL

(a) To show all information about the patients of cardiology department.

  (b) To list the names of female patients who are in ENT department.

  (c) To list names of all patients with their date of admission in ascending order.

  (d) The display Patients' name, charges age for female patients only.

  (e) To count the number of patients with age<30.

  (f) To insert a new row in the HOSPITAL table with the following data :

      11, "Aftab" 24, "Surgery", (25/02/98), 300, "M"

  (g) Give the output of the following SQL statements :

     (i) Select COUNT (DISTINCT Charges) form HOSPITAL.

     (ii) Select MIN(Age) from HOSPITAL where Sex = "F".

     (iii) Select SUM9Charges) form HOSPITAL where Department = "ENT".

     (iv) Select AVG(CHARGES) from HOSPITAL where Dateofadm < (12/02/98).

Ans.(a) SELECT * FROM HOSPITAL WHERE department = "Caddiology";

    (b) SELECT name FROM HOSPITAL WHERE department = "ENT" AND sex="F";

    (c) SELECT name FROM HOSPITAL ORDER BY dateofadm ;

    (d) SELECT name, charges, age FROM HOSPITAL WHERE SEX = "F" ;

    (e) SELECT COUNT (*) FROM HOSPITAL WHERE age <30 ;

    (f) INSERT INTO HOSPITAL VALUES (11, "Aftab", 24, "Surgery",            {25/02/98}, 300, "M" ;

    (g) (i) 5 (ii) 16 (iii) 750 (iv) 350

2. Write the SQL for (a) to (f) and write the outputs for (h) on the basis of the table HOSPITAL :

TABLE : HOSPITAL

(a) To show all information about the patients of cardiology department.

(b) To list the name of female patients who are in orthopaedic department.

(c) To list names of all patients with their date of admission in ascending order.

(d) The display Patient's name, charges, age for male patients only.

(e) To count the number of patients with age>20.

(f) To insert a new row in the HOSPITAL table with the following data :

    11, "Mustafa", 37, "ENT", (25/02/98), "M".

(g) Give the output of following SQL statement :

   (i) Select COUNT (DISTINCT Charges) form HOSPITAL.

   (ii) Select MIN (Age) from HOSPITAL where Sex = "M".

   (iii) Select SUM (Charges) from HOSPITAL where Sex = "F".

   (iv) Select AVG (Charges) from HOSPITAL where Dateofadm < (12/02/98).

Ans. (a) SELECT * FROM HOSPITAL WHERE department = "Cardiology" ;

     (b) SELECT name FROM HOSPITAL WHERE department = "Orthopedic" and 

         SEX = "F" ;

     (c) SELECT name FROM HOSPITAL ORDER BY dateofadm ;

     (d) SELECT nams, charges, age FROM HOSPITAL WHERE SEX = "M" ;

     (e) SELECT COUNT (*) FROM HOSPITAL WHERE age > 20 ;

     (f) INSERT INTO HOSPITAL VALUES( 11, "Mustafa", 37, "ENT", {25/02/98,

         250, "M" ) ;

     (g) (i) 5 (ii) 12 (iii) 1600 (iv) 387.50

3. Write the SQL commands for delete (a) to (f) and write the outputs for (g) on the basis of table STUDENT :

TABLE : STUDENT

(a) To show all information about the students of history department.

(b) To list the name of female students who are in Hindi department.

(c) To list the name of female students with their date of admission in ascending order.

(d) The display Student's Name, charges, age for female students only.

(e) To count the number of students with age>23.

(f) To insert a new row in the STUDENT table with the following data :

   9, "Zaheer", 36, "Computer", (12/03/97), 230, "M".

(g) Give the output of the following SQL statements :

   (i) Select COUNT (DISTINCT department) from STUDENT.

   (ii) Select MAX (Age) from STUDENT where Sex = "F".

   (iii) Select AVG (Fees) from STUDENT where SEX = "M".

   (iv) Select SUM (Fees) from STUDENT where Dateofadm < (01/01/98).

Ans. (a) SELECT * FROM STUDENT WHERE department="History";

     (b) SELECT name FROM STUDENT WHERE department="Hindi" and SEX="F";

     (c) SELECT name FROM STUDENT ORDER BY dateofadm;

     (d) SELECT name, charges, age FROM STUDENT WHERE sex="M";

     (e) SELECT COUNT (*) FROM STUDENT WHERE AGE>23;

     (f) INSERT INTO STUDENT VALUES(9,"ZAHEER",36,"Computer",{12/03/97,230,"m");

     (g) (i) 3 (ii) 25 (iii) 236 (iv) 1080 

4. Write the SQL for (a) to (f) and write the output of the (g) in the basis of the table TEACHER :

TABLE : TEACHER

 (a) To show all information about the teacher of history department.

 (b) To list the name of female teachers who are in Maths department.

 (c) To list names of all teachers with their date of admission in ascending order.

 (d) Display teacher's Name, Salary, age of female teachers only.

 (e) To count the number of teacher with age>23.

 (f) To insert a new row in the TEACHER table with the following data :

      9, "Raja", 26, "Computer", {13/05/95}, 2300, "M".

 (g) Give the output of the following SQL statements :

    (i) Select COUNT (DISTINCT department) form TEACHER ;

    (ii) Select MAX (Age) from TEACHER where SEX = "F" ;

    (iii) Select AVG (Salary) from TEACHER where SEX = "M" ;

    (iv) Select SUM (Salary) from TEACHER where DATEOFJOIN < {12/07/96} ;

Ans.(a) SELECT * FROM TEACHER WHERE department = "History" ;

    (b) SELECT name FROM TEACHER WHERE department="History" and SEX="F";

    (c) SELECT name FROM TEACHER ORDER BY dateofjoin ;

    (d) SELECT name, salary, age FROM TEACHER WHERE sex = "M" ;

    (e) SELECT COUNT (*) FROM TEACHER WHERE age > 23

    (f) INSERT INTO TEACHER VALUES (9, "Raja", 26, "Computer", {13/05/95},

        2300, "M" ) ;

    (g) (i) 3 (ii) 35 (iii) 20050.00 (iv) 2300  

5. Write SQL commands for (a) to (d) and write the outputs for (e) and (f) on the basis of table CLUB :

Table : CLUB

(a) To show all information about the swimming coaches in the club.

(b) To list names of all coaches with their date of appointment (DATEOFAPP) in descending order.

(c) To display a report, showing coach name, pay, age and bonus (15% of pay) for all the coaches.

(d) To insert a new row in the CLUB table with the following data :

(e) Give the output of the following SQL statements :

   (i) Select COUNT (distinct SPORTS) from CLUB.

   (ii) Select MIN (AGE) from CLUB where SEX = "F".

   (iii) Select AVG (PAY) from CLUB where SPORTS = "KARATE".

   (iv) Select SUM (PAY) from CLUB where DATEOFAPP > {31/01/98);

(f) Assume that there is one more table COACHES in the database as shown below :

TABLE : COACHES

What will be the output of the following query :

SELECT      SPORTSPERSON

COACH       NAME

FROM        CLUB, COACHES

WHERE       COACH_ID = COACH_NO

Ans. (a) SELECT * FROM CLUB WHERE SPORTS = " swimming " .

     (b) SELECT COACHNAME FROM CLUB ORDER BY DATEOFAPP DESC

     (c) SELECT COACHNAME, PAY, AGE, Pay * 15/100 FROM CLUB

     (d) INSERT INTO CLUB VALUES ( 11, "Rajiv", 40, "Hockey", {27/05/2000'

         2000, "M")

     (e) (i) 4 (ii) 34 (iii) 1100 (iv) 7800

     (f) SPORTS PERSON        COACHNAME

         VINOD                 KUKREJA

         AJAY                  KUKREJA

         SEEMA                 RAVINA

         TANEJA                KARAN

6. Write SQL commands for (a) to (d) and write the outputs for (e) and (f) on the basis of table GRADUATE :

Table : GRADUATE

(a) List the names of those students who have obtained Div I sorted by NAME

(b) Display the report, listing NAME, STIPENED, SUBJECT and amount of stipend received in a year assuming that the STIPENED is paid every month.

(c) To count the number of students who are either PHYSICS or COMPUTER SC graduates.

(d) To insert a new row in the GRADUATE table :

    11, "KAJOL", 300, "COMPUTER SC", 75, 1.

(e) Give the output of following SQL statements based on table GRADUATE :

   (i) Select MIN (AVERAGE) from GRADUATE where SUBJECT = PHYSICS";

   (ii) Select SUM (STIPEND) from GRADUATE where DIV = 2;

   (iii) Select AVG (STIPEND) from GRADUATE wheree AVERAGE > = 65";

   (iv) Select COUNT (DISTINCT SUBJECT) from GRADUATE;

(f) Assume that there is one more table GUIDE inm the database as shown below :

Table : GUIDE

What will be the output of the following query :

SELECT      NAME ADVISOR

FROM        GRADUATE GUIDE

WHERE       SUBJECT = MAINAREA

Ans. (a) SELECT NAME FROM GRADUATE WHERE DIV = 1 ORDER BY NAME

     (b) SELECT NAME, STIPEND, SUBJECT, STIPEND * 12 FROM GRADUATE

     (c) SELECT COUNT (*) FROM GRADUATES where (SUBJECT = "PHYSICS" OR

         SUBJECT = "COMPUTER SC")

     (d) INSERT INTO GRADUATE VALUES (11, "KAJOL", 300, :COMPUTER SC:, 

         75.1)

     (e) (i) 63 (ii) 1000 (iii) 450 (iv) 4

     (f) NAME            ADVISOR

         KARAN           VINOD

         DIVAKAR         ALOK

         DIVYA           RAJAN

         ARUN            VINOD

         SABINA          MAHESH

         JOHN            RAJAN

         ROBERT          VINOD

         RUBINA          MAHESH

         VIKAS           ALOK

         MOHAN           MAHESH       

7. Write SQL commands for (a) to (d) and write the outputs for (e) and (f) on the basis of table EMPLOYEE.

TABLE : EMPLOYEE

(a) List the names of the employees who are more than 34 years old sorted by NAME.

(b) Display a report, listing NAME, BASIC, DEPARTMENT and annual salary. Annual salary equals BASIC X 12.

(c) To count the number of employees who are either working in PERSONNEL or COMPUTER department.

(d) To insert a new row in the EMPLOYEE table :

    11, "VIJAY", 9300, "FINANCE", {13/07/98}, 35, "M"

(e) Give the output of the following SQL statement based on table EMPLOYEE

   (i) Select SUM (BASIC) from EMPLOYEE where DEPARTMENT = "PERSONNEL";

   (ii) Select AVG (BASIC) from EMPLOYEE where SEX = "F";

   (iii) Select MAX (BASIC) from EMPLOYEE where DATOFAPP > (22/02/97);

   (iv) Select COUNT (DISTINCT DEPARTMENT) from EMPLOYEE;

(f) Assume that there is one more table INCHARGE in the database as shown below ;

TABLE : INCHARGE

What will be the output of the following query ;

SELECT      NAME, HEAD

FROM        EMPLOYEE, INCHARGE

WHERE       DEPARTMENT = DEPT

Ans. (a) SELECT * FROM EMPLOYEE WHERE AGE > 34 ORDER BY NAME ;

     (b) SELECT NAME, BASIC, DEPARTMENT, BASIC * 12 FROM EMPLOYEE

     (c) SELECT COUNT (*) FROM EMPLOYEE WHERE DEPARTMENT = " PERSONNEL "

         OR DEPARTMENT = " COMPUTER " ;

     (d) Insert into EMPLOYEE values (11, "VIJAY", 9300, "FINANCE", 

         {13/07/98}, 35, 'M') ;

     (e) (i) 24600 (ii) 8750 (iii) 9500 (iv) 5

     (f) NAME        HEAD

         KARAN       RAHUL

         ARUN        RAHUL

         ROBERT      RAHUL

         DIVAKAR     SATYAM

         VIKAS       SATYAM

         DIVYA       NATH

         SABINA      NATH

         JOHN        GANESH

         VIJAY       GANESH

         RUBINA      JACOB

         MOHAN       JACOB

8. (a) Write SQL commands for (i) to (vii) on the basis of the table STUDENT

Table : STUDENT

(i) Display the names of the students who are getting grade 'C' in either GAME or SUPW

(ii) Display the number of students getting grade 'A' in Cricket

(iii) Display the different games offered in the school.

(iv) Display the SUPW taken up by the students, whose name starts with 'A'.

(v) Add a new column named 'Marks'.

(vi) Assign a value 200 for Marks for all those who are getting grade 'B' or above in GAME.

(vii) Arrange the whole table in the alphabetical order of SUPW.

(b) If R1 is a relation with 5 rows and R2 is a relation with 3 rowes, how many rows will the Cartesian product of R1 and R2 have?

Ans. (a) (i) SELECT name FROM student WHERE grade = 'C' OR grade 1 = 'C'

         (ii) SELECT COUNT (grade) FROM student WHERE grade = 'A' AND 

              game = 'Cricket'

         (iii) SELECT DISTINCT game FROM student

         (iv) SELECT supw FROM student WHERE name LIKE 'A%'

         (v) ALTER TABLE student ADD (marks NUMBER (3))

         (vi) UPDATE student SET marks = 200 WHERE grade IN ('A','B')

         (vii) SELECT * FROM student ORDER BY supw

     (b) 15

9. Write SQL commands for (i) to (vii) on the basis of the table SPORTS.

Table ; SPORTS

(i) Display the names of the students who have grades 'C' in either Game1 or Game2 or both.

(ii) Display the number of students getting grade 'A' in Cricket.

(iii) Display the names of the students who have same game for both Game1 and Game2.

(iv) Display the games taken up by the students, whose name starts with 'A'.

(v) Add a new column named 'Marks'.

(vi) Assign a value 200 for Marks for all those who are getting grade 'B' or grade 'A' in both Game1 and Game2.

(vii) Arrange the whole table in the alphabetical order of Name.

Ans. (i) SELECT NAME FROM SPORTS WHERE GRADE = 'C' OR grade1 = 'C'

     (ii) SELECT COUNT (*) FROM sports WHERE (game1 = "Cricket" AND grade

         = 'A' OR (game2 = "Cricket" AND game1 = 'A')

     (iii) SELECT name FROM sports WHERE game1 = game2

     (iv) SELECT game1, game2 FROM sports WHERE name LIKE "A%"

     (v) ALTER TABLE SPORTS ADD (MARKS NUMBER (3))

     (vi) UPDATE sports SET marks = 200 WHERE grade = 'A' OR grade = 'B' 

         OR grade1 = 'A' OR grade1 = 'B'    

     (vii) SELECT * FROM sports ORDER BY name

10. Differentiate between Drop table and Drop view command.

Ans. Drop Table. This command is used to remove the table physically.

     Drop View. This command is used to delete a view from a database.

11. Write SQL commands for (a) to (f) and write the outputs for (g) on the basis of table STUDENT.

STUDENT

(a) List the name of all the students who have taken stream as COMPUTER.

(b) To count the number of female students.

(c) To display the number of students streamwise.

(d) To insert a new row in the STUDENT table :

     9, "KARISHMA", "ECONOMICS", 300, 18, "F".

(e) To display a report, listing NAME, STREAM, SEX and stipend where stipend is 20% of fees.

(f) To display all the records in stored order of name.

(g) Give the output of the following SQL statements based on STUDENT table;

    (i) Select AVG (fees) from STUDENT where stream = "COMPUTER".

    (ii) Select MAX (age) from STUDENT;

    (iii) Select count (distinct STREAM) from STUDENT;

    (iv) Select sum (fees) from STUDENT group by stream.

Ans. (a) SELECT NAME FROM STUDENT WHERE STREAM = "COMPUTER"

     (b) SELECT COUNT (*) FROM STUDENT WHERE SEX = "F"

     (c) SELECT STREAM, COUNT (*) FROM STUDENT GROUP BY STREAM

     (d) INSERT INTO STUDENT VALUES (9, "KARISHMA", "ECONOMICS", 300, 18.

         "F" )

     (e) SELECT NAME, STREAM, SEX, FEES * 2 FROM STUDENT

     (f) SELECT * FROM STUDENT ORDER BY NAME

     (g) The output is given after inserting a record of (d).

         (i) 75000

         (ii) 19

         (iii) 4

         (iv) 1000.00

              2250.00

              900.00

              350.00

IMPORTANT QUESTIONS [1 and 2 Marks]

12. Create a table called STUDENT having following fiel

        NAME        CHAR(25)

        ROLL        NUMBER(3)

        AGE         NUMBER(2)

        MARKS       FLOAT(5)

Ans. CREATE TABLE STUDENT

      (NAME C(25),

      ROLL N(3),

      AGE   N(2),

     MARKS  N(5); 

13. Insert the following data into the STUDENT table :

Ans. INSERT INTO STUDENT VALUES ("Amit", 102, 18, 450)

     INSERT INTO STUDENT VALUES ("Rahul", 121, 19, 370)

     INSERT INTO STUDENT VALUES ("Rohit", 122, 18, 390)

14. Create a table called Supplier with the columns specified below ;

        Pname        CHAR(20)

        Sname        CHAR(20)

        Qty          NUMBER(3)

        Price        FLOAT(5,2)

        City         CHAR(20)

Ans. Try yourself.

15. Insert the following data into the SUPPLIER table :

Ans. Try yourself

16. Create a table bank with the columns specified below :

        Ace_no        NUMBER(3)

        CName         CHAR(20)

        Bname         CHAR(30)

        Amount        FLOAT(10,2)

        Dateofopen    DATE

        T_Transaction NUMBER(3)

Ans. Try yourself.

17. Insert the following data into the table Bank :

Ans. Try yourself.

18. Write the SQL command for (a) to (f) and the write the output of the (g) on the basis of the table SUPPLIER :

TABLE : SUPPLIER

(a) Display data for all products whose quantity is between 170 and 370.

(b) Display data for all products sorted by their quantity.

(c) Find all the products that have no supplier.

(d) Give Sname for that entire product whose name starts with "C".

(e) To list Sname, Pname, Price for all the products whose quantity is <200.

(f) To display S#, Pname, Sname, Qty in reverse order of qty from the SUPPLIER table.

(g) Give the output of the following SQL commands :

        (i) Select AVG (Price) from SUPPLIER where price <30

        (ii) Select MAX (Price) from Supplier where price >30

        (iii) Select SUM (Price* Qty) from Supplier where Qty <10

        (iv) Select COUNT (DISTINCT city) FROM supplier.

(h) Assume that there is one more table PRODUCT in the database as shown below :

Table : PRODUCT

What will be the output of the following query :

    Select Pname, P#

      From Supplier, Product

      Where City = Pcity

Ans.(a) SELECT * FROM SUPPLIER WHERE qty BETWEEN 170 and 370 ;

    (b) SELECT * FROM SUPPLIER ORDER BY qty ;

    (c) SELECT * FROM SUPPLIER WHERE sname = "" ;

    (d) SELECT sname FROM SUPPLIER WHERE pname LIKE "C%" ;

    (e) SELECT sname, pname, price FROM SUPPLIER WHERE wty < 200 ;

    (f) SELECT no, sname, pname, price, qty FROM SUPPLIER ORDER BY qty DESC

    (g) (i) 12.33 (ii) 45.00 (iii) 0 (iv) 5

    (h) Pname        P#

        Bread        P2

        Bread        P1

        Chocolate    P2

        Chocolate    P1

        Souce        P4

        Maggi        P3

        Jam          P2

        Jam          P1

        Piknik       P3       

19. Write the SQL command for (a) to (f) and write the output of the (g) on the basis of the table BANK :

TABLE : BANK

(a) Display data for all Customers whose transaction is between 8 and 11.

(b) Display data for all Customers sorted by their dateofopen.

(c) To count the number of customers with amount <30000.

(d) List the minimum and maximum amount from the BANK.

(e) To list Cname, Bname, Amount for all the clients whose amount is <20000.

(f) To display Ace_no, Cname, Bname, Total transaction in reverse order of amount.

(g) Give the output of the following SQL commands :

    (i) Select AVG (Amount) from BANK where amount <23000.

    (ii) Select MAX (Amount) from BANK where amount >30000.

    (iii) Select SUM (T_Transaction) from BANK.

    (iv) Select COUNT (DISTINCT Bname) FROM BANK.

(h) Consider another table CUSTOMER in the database as shown below :

Table : CUSTOMER

What will be the output of the following query :

Select Cname, TBank

    From bank, Customer

    Where Cname = Tname

Ans. (a) SELECT * FROM BANK WHERE t_transact between 8 and 11 ;

     (b) SELECT * FROM BANK ORDER BY DATAOPEN ;

     (c) SELECT COUNT (*) FROM BANK WHERE amount < 30000;

     (d) SELECT MIN (amount), MAX (amount) FROM BANK ;

     (e) SELECT cname, bname, amount FROM BANK WHERE amount < 20000 ;

     (f) SELECT ace_n0, cname, bname, t_transaction FROM BANK ORDER BY

          amount DESC ;

     (g) (i) 18000 (ii) 56000 (iii) 88 (iv) 5

     (h) Cname        Tbank

         Puneet       Uco Bank

         Yatin        Standard Chartered

         Sunny        State Bank

         Nikhil       Bank of Baroda

20. Write the SQL command for (a) to (f) and write the output of the (g) on the basis of the table VOTER :

TABLE : VOTER

(a) Write a SQL statement to list V#, Vname, Age for all the voters. This information should be sorted on Vname.

(b) To list all those employees who either reside in Rohini or whose age <25.

(c) Display all the voters with age >27.

(d) List different voters with unique age.

(e) Count the number of voters where address is 'Rohini'.

(f) Insert a new voter in the VOTER table. Fill the entire column with values.

(g) Give the output of following SQL commands :

    (i) Select SUM (Age) from VOTER

    (ii) Select COUNT (DISTINCT age) from VOTER

    (iii) Select MAX (age) from VOTER where age ,26

    (iv) Select MIN (age) from VOTER where address = "Paschim Vihar".

Ans. (a) SELECT vno, vname, age FROM VOTER ORDER BY vname ;

     (b) SELECT * FROM VOTER WHERE address = "Rohini" OR age < 25 ;

     (c) SELECT * FROM VOTER WHERE age > 27 ;

     (d) SELECT DISTINCT (age) VOTER ;

     (e) SELECT COUNT (*) FROM VOTER WHERE address = "Rohini" ;

     (f) INSERT INTO VOTER VALUES (11, "Ramesh", 32, "Rohini", 7058318) ;

     (g) (i) 248 (ii) 7 (iii) 24 (iv) 24

21. Write the SQL command for (a) to (f) and write the output of the (g) on the basis of the table DIRECTORY :

TABLE : DIRECTORY

(a) To select all the information of employees of Rohini area.

(b) Update the database set the phone no. as 7047645 where phone number is 7057456.

(c) To create a view called Dir with the following fields Fname, Phone, Address.

(d) To display the data for Arpit, Rahul and Kisan.

(e) To delete the rows where the address is Rohini.

(f) To delete the table physically.

(g) Give the output of the following SQL table :

    (i) Select COUNT (DISTINCT address) FROM directory

    (ii) Select Fname, Lname from DIRECTORY where phone = 7026519

    (iii) Select Phone from DIRECTORY where address like "V%"

    (iv) Select Fname, Phone From DIRECTORY where Fname like "R----"

Ans. (a) SELECT * FROM DIRECTORY WHERE address = "Rohini" ;

     (b) UPDATE DIRECTORY SET phone = "7057456" ;

     (c) CREATE VIEW DIR AS SELECT fname, phone, address FROM directory ;

     (d) SELECT * FROM directory WHERE fname IN ("Arpit", "Rahul", "Kisan")

     (e) DELETE FROM directory WHERE address = "Rohini" ;

     (f) DROP TABLE directory ;

     (g) (i) 5 (ii) Rakesh Gulati (iii) 5563412

         (iv)   Fname            Phone

                Ram              5563412

                Rohit            2235434

                Rahul            7057456 

22. Write SQL commands for (a) to (f) and write the outputs for (g) on the basis of table STUDENT :

TABLE : STUDENT

(a) Select all the Nonmedical stream students from STUDENT.

(b) List all the names of those students who are in class 12 sorted by stipend.

(c) List all students sorted be avgMark in descending order.

(d) Display a report, listing name, Stream and amount of stipend received in a year assuming that the stipend is paid every month.

(e) To count the number of students with grade = "A".

(f) To insert a new student in the STUDENT table and fill the entire column with some valuesw.

(g) Give the output of the following SQL statements :

    (i) Select MIN (AvgMark) from STUDENT where AvgMark <75

    (ii) Select SUM (Stipend) from STUDENT where Grade = "B"

    (iii) Select AVG (Stipend) from STUDENT where Class = "12A"

    (iv) Select COUNT (DISTINCT stream) from STUDENT

Ans. (a) SELECT * FROM STUDENT WHERE stream = "Nonmedical" ;

     (b) SELECT name FROM STUDENT WHERE class LIKE "12" ORDER BY stipend ;

     (c) SELECT * FROM STUDENT ORDER BY AVGMARK DESC ;

     (d) SELECT name, stipend, stream, stipend*12 FROM STUDENT ;

     (e) SELECT COUNT (*) FROM STUDENT WHERE grede = "A" ;

     (f) INSERT INTO STUDENT VALUES (11, "Pankag", 400, "Medical", 89.90,

         "A", "12C") ;

     (g) (i) 64.40 (ii) 1150.00 (iii) 475.00 (iv) 4  

23. Write SQL commands for (a) to (f) and write the outputs for (g) on the basis of table :

TABLE : MOV

(a) Find the total value of the movie cassettes available in the library.

(b) Display aq list of all movies with price over 20 and sorted by price.

(c) Display all the movies stored by Qty in descending order.

(d) Display a report listing a movie number, current value and replacement value for each movie in the above table. Calculate the replacement value for all movies as QTY * PRICE * 1.15

(e) Count the number of movies where rating is not "G".

(f) Insert a new movie in the MOV table. Fill the entire column with values.

(g) Give the output of the following SQL commands :

    (i) Select AVG (Price) from MOV where Price <30

    (ii) Select MAX (Price) from MOV where Price >30

    (iii) Select COUNT (DISTINCT TYPE) FROM MOV

Ans. (a) SELECT SUM (qty * price) FROM MOV ;

     (b) SELECT * FROM MOV WHERE PRICE > 20 ORDER BY PRICE ;

     (c) SELECT * FROM MOV ORDER BY qty DESC ;

     (d) SELECT no, qty * price 1.15 FROM MOV ;

     (e) SELECT COUNT (*) FROM MOV WHERE rating <> "G" ;

     (f) INSERT INTO MOV VALUES (11, "100 DAYS", "Horror", "R", "John", 

          6, 43.22) ;

     (g) (i) 26.62 (ii) 69.95 (iii) 793.10 (iv) 4    

24. Write the SQL commands for (a) to (f) and write the output of (g) on the basis of table :

TABLE : PRODUCT

(a) Display data for the entire item sorted by their name.

(b) Display the Name and Price from the table item in reverse order of their stock.

(c) List all Name and Price with Price between 3000 and 7000.

(d) Write the command to set the price field for all products to 1200 corresponding to NAME = "Keyboard".

(e) Write the SQL command to delete rows with stock between 20 to 40.

(f) To count the number of products with stock <5.

(g) Give the output of the following SQL command :

    (i) Select SUM (Stock) from PRODUCT.

    (ii) Select AVG (Stock) from PRODUCT where stock >20.

    (iii) Select MAX (Price) from PRODUCT.

Ans. (a) Select * from PRODUCT ORDER BY Name ;

     (b) Select Name, Price FROM PRODUCT ORDER BY stock DESC :

     (c) Select Name, Price FROM PRODUCT WHERE price BETWEEN 3000 AND 7000

     (d) UPDATE PRODUCT SET price = 1200 WHERE Name = "Keyboard" ;

     (e) DELETE FROM PRODUCT WHERE stock BETWEEN 20 AND 40 ;

     (f) SELECT COUNT (Stock) FROM PRODUCT WHERE stock < 5

     (g) (i) 289 (ii) 43.14 (iii) 8 (iv) 7900

25. Write the SQL command for (a) to (f) and output for (g) with the help of table shown below :

TABLE : HOSPITAL

 (a) To select all the information of patients of cardiology department.

(b) To list the names of female patients who are in ENT department.

(c) To list names of all patients with their date of admission in ascending order.

(d) To display Patient's Name, Charges, Age for only female patients.

(e) To count the number of patients with Age >30.

(f) To insert a new row in the HOSPITAL table with the following data :

    11, "Aftab", 24, "Surgery", {25/02/98}, 300, "M".

(g) Give the output of the following SQL statements :

    (i) Select COUNT (DISTINCT charges) from HOSPITAL

    (ii) Select MIN (Age) from HOSPITAL where Sex = "F";

    (iii) Select SUM (charges) from HOSPITAL where Department = "ENT";

    (iv) Select AVG (charges) from HOSPITAL where Dateofadm < {12/02/98};

Ans. Try yourself.

26. Write the SQL commands for (a) to (f) and output for (g) with the help of table shown below :

Table : EMPLOYEE

 (a) To select all the information of employee of dept number 20.

 (b) Find all employees who are either Clerk or who earn between 1000 and 2000.

 (c) To list names of all employees in ascending order of their salary.

 (d) To display employee's Name, Charges, Age for only Managers.

 (e) To count the number of employees with salary < 3000.

 (f) To insert a new row in the EMPLOYEE table with the following data :

     11, "MILLER", "Manager", 7698, {25/02/98}, 4300, 20

 (g) Give the output of following SQL statements :

    (i) Select COUNT(*) from EMPLOYEE;

    (ii) Select MIN (Salary) from EMPLOYEE where deptno = 20;

    (iii) Select SUM (Salary) from EMPLOYEE where Department = "Clerk";

    (iv) Select AVG (Salary) from EMPLOYEE

Ans. Try yourself.

27. What is the difference between CREATE VIEW and CREATE TABLE command?

Ans. 

28. NOTE : Write SQL commands for the queries (a) to (f) and write the output of the SQL commands given in part (g) based on a relation SHOP shown below :

Relation : SHOP

(a) To display the names of all shops which are in the area South.

(b) To display name and Customer Percentage of all the shops having cust_percent >80

(c) To display list of all shops with sale > 300000 in ascending order of Shop_Name.

(d) To display a report with Shop_Name, Area and Rating for each shop in the table, for only those shops whose sale is between 350000 and 400000 (including both 350000 and 400000).

(e) To display the City and the number of shops in each city in the table SHOP.

(f) To insert details of a new shop in the table SHOP with the following data :

    7, "The Shop", 550000, "South", 90.8, 'A', "Ahmedabad"

(g) Write the output of the following :

    (i) Select Min (Cust_percent) from SHOP;

    (ii) Select Sum (Sale) from SHOP where Rating = 'A';

    (iii) Select Avg (Sale) from SHOP where City = "Delhi";

    (iv) Select Count (Distinct City) from SHOP;

Ans. (a) SELECT SHOP_NAME FROM SHOP WHERE AREA = "sOUTH"

     (b) SELECT SHOP_NAME, CUST_PERCENT FROM SHOP WHERE CUST_PERCENT >80

     (c) SELECT SHOP_NAME FROM SHOP WHERE SALE > 300000 ORDER BY SHOP_NAME

     (d) SELECT SHOP_NAME, AREA, RATING FROM SHOP WHERE SALES BETWEEN 

         350000 AND 400000

     (e) SELECT CITY, COUNT (DISTINCT SHOP_NAME) FROM SHOP GROUP BY CITY

     (f) INSERT INTO SHOP VALUES (7, "The Shop", 550000, "South", 90.8,

         'A', "Ahmedabad")

     (g) The resulted output displays after inserting a record of (h)

        (i) 66.70 (ii) 1796000 (iii) 353000 (iv) 4  

29. What is the difference between DDL and DML ?

Ans. DDL.:Those commands which are used to create and maintain database are

          grouped into the class called Data Definition Languages (DDL).

     DML. :Those commands which are used to perform query are grouped into

           the class called Data Manipulation Language (DML). 

30. Define view.

Ans. A view is table that has no data of its own. A view is derived from

     another table.

31. What is the difference between create view and create table ?

Ans. The create view command is used to create the view. This command  

    can directly copy the data from the table from which the the view

    is derived. So, there is no need to insert the data using the INSERT

    command. The create table command is used to create the table. A table

    is a permanent database. So, we use the INSERT command to insert the

    records in table.

32. Write few features of SQL.

Ans. The main features of SQL commands are :

    (i) Recovery and concurrency :Concurrency is concerned with the manner 

       in which multiple users operate upon the database. Each user can 

      either reflect the updates of a transaction or can cancel all the

      updates of a transaction.

    (ii) Security :The security can be maintained by view mechanism. A view

        is used to hide sensitive information and defines only part of a

        table which should be visible.

    (iii) Integrity constraints :Integrity constraints are enforced by the

          system. For example, one can specify that an attribute of a 

          relation will not take on null values. 

33. What are the rules for SQL commands ?

Ans. When writing SQL commands, it is important to remember few simple 

     rules and guidelines in order to construct valid statements that are

     easy to read and edit :

   (i) SQL commands may be on one or many lines.

   (ii) Clauses are usually placed on separate lines.

   (iii) Tabulation can be used.

   (iv) Commands words cannot be split across lines.

   (v) SQL commands are not case sensitive (unless indicated otherwise)

   (vi) An SQL command is entered at the FoxPro Windows command prompt.

   (vii) Only one ststement can be current at any time within the buffer,

        and it can be run in continuous line by placing a semicolon (;)

        at the end of last clause. 

34. What are the rules to name a table ?

Ans. Rules to name a Table ;

    (i) The name must begin with a letter A-Z or a-z.

    (ii) It may contain letters, digits and special character undercore

        (_), $ and # but the use of $ and # is always discouraged.

    (iii) You can use either uppercase or lowercase letters, both are same

    (iv) The length of table name is upto 30 characters.

    (v) The name must not be a SQL reserved word. 

35. What is the difference between the Unique and Primary key clause ?

Ans. The unique key clause used to designate a column as a unique value.

     No two rows in the table can have the same value for that key. As

     with unique key, primary key is also used to enforce uniqueness. But

     the only difference is that ther is only one primary key per table

     allowed and there may be number of unique keys in the table.

36. What is the difference between column constraint and the table constraint ?

Ans.When a constant is applied on a single column of the table, it is      

    called column constraint. When a constraint is applied on a group

    of columns of the table. It is called table constraint. 

37. What will be the precondition for applying Drop Table command ?

Ans. The precondition for applying Drop Table command is that the table

     should be empty.

38. What do you understand by the aggregate function ?

Ans. The aggregate functions are group functions. They return result base 

    on group of rows, rather than one result per row as returned by single

    row functions.

39. What conditions are satisfied while using union clause ?

Ans. We can combine multiple queries by using UNION clause. But the  

    condition to apply union clause is that both the tables should have

    same structure and specify the column name of both the tables in the

    same sequence.

40. What is the difference between CREATE CURSOR and CREATE TABLE command ?

Ans. 

41. Write SQL commands for the queries (a) to (f) and write the output of the SQL commands given in part (g) based on a relation STUDENT shown below

Relation : STUDENT

(a) To display the names of all students who are in Medical Stream.

(b) To display name and average of all the students having AvgMark <70.0.

(c) To display list of all the students with Stipend >350.00 in ascending order of Name.

(d) Do display a report with Name, Marks for each student in the table. Marks are calculated as AvgMark *5.

(e) To display the Stream and the number of students in each Stream in the table STUDENT.

(f) To insert a new student in the table STUDENT with the following data :

     7, "Radh", "a", 500.00, "Commerce", 90.8, 'A', "12A";

(g) Write the output of the following :

    (i) Select Min (AvgMark) from STUDENT;

    (ii) Select Sum (Stipend) from STUDENT where Grade = 'A';

    (iii) Select Avg (Stipend) from STUDENT where Stream = "Commerce";

    (iv) Select Count (Distinct Stream) from STUDENT ;

Ans. (a) SELECT NAME FROM STUDENT WHERE STREAM = "Medical"

     (b) SELECT NAME, AVGMARK FROM STUDENT WHERE AVGMARK < 70

     (c) SELECT * FROM STUDENT WHERE stipend > 350 order by name

     (d) SELECT NAME, AVGMARK * 5 FROM STUDENT

     (e) SELECT STREAM, COUNT (STREAM) FROM STUDENT GROUP BY STREAM

     (f) INSERT INTO STUDENT VALUES (7, "Radhika", 500.00, "Commerce",

          90.8, 'A', "12A")

     (g) The resulted output displays after inserting a record of (h)

        (i) 64.40 (ii) 1900.00 (iii) 400 (iv) 4

42. Describe any three clauses, which can be used with select statement.

Ans. (i) FROM clause;The required FROM clause specifies the tables or

         views that are used to retrieve values.

        Syntax SELECT column name1,column name2,.......

               FROM table_name;

     (ii) WHERE clause: The WHERE clause specifies the search 

          condition used to determine the the data that will 

          appear in the result table. You can use any  of the

          comparison operators (Operators are <,>,<=,>=,=,<>,

          and #)The search condition can use any valid expression

          Include parentheses as neede to achieve the desired result

          Syntax SELECT column name1,column name2,.......

                 FROM     table name

                [WHERE    condition];

     (iii) GROUP BY clause: The GROUP BY clause groups the rows in the

           result table by columns that have the same values, so that

           each group is reduced to a single row. Each item is separated 

           by a comma.

           Syntax SELECT column1, ccolumn2,...

                  FROM     table name

                [GROUP BY   column name];                    

43. Create a database named as MEMBER using SQL command of following structure :

    FieldName    FieldType    Width    Decimal    Description

    mem_code     Character      3                 Member Code

    mem_name     Character      30                Member name

    mem_add      Character      50                Member address

    mem_phone    Character       7                Member contact number

    no_of_bk     Number          3                Number of books stock

    no_of_iss    Number          3                Number of books issued

  Answer the following using above table :

    (i) Display all distinct records using SQL command.

    (ii) Create a new table using SQL commands from the existing table MEMBER.

    (iii) Display the report mem_code wise from the table MEMBER.

    (iv) Display all the records from the MEMBER price wise in descending order.

    (v) Display the details of all the members whose name starts with letter P.

    (vi) Display all the records whose mem_code is between 100 to 400.

    (vii) Display the records of a member whose phone number is 7771234.

    (viii) Add new field in the table called F_no_mem of Numeric type and width is 3.

    (ix) Delete all the rows of a table.

    (x) Drop the table.

Ans. Try yourself.

44. What is the difference between CREATE CURSOR and CREATE TABLE command ?

Ans. See Ans.40 of Important Questions [1 and 2 Marks].

45. If R1 is a relation with 5 rows and R2 is a relation with 3 rows, how many rows will the Cartesian product of R1 and R2 have ?

Ans. 15.

46. Explain Cartesian product of two relations.

Ans. The Cartesian product of two relations is the concatenation of tuples

     belonging to the two relations.the Cartesian product is a binary

     operation and is denoted by (x). The degree of new relation is the sum

     of the degrees of two relations on which Cartesian product is 

     operated. The number of tuples of the new relation is equal to the 

     product of the number of tuples of the two relations on which

     Cartesian product is performed.

47. Write SQL commands for the queries (a) to (f) and write the output of the SQL commands given in (g) based on a relation EMPLOYEE shown below :

RELATION : EMPLOYEE

(a) To display the names of all employees who are in the area South.

(b) To display name and age of all employees having age >40.

(c) To display list of all employees whose salary >= 30000 and <=40000.

(d) To display the lst department wise.

(e) To display the employee names in descending order of age.

(f) To insert a new row with the following data :

    7, "TARIK SINGH", 45000, "SOUTH", 45, "C", "ELEC".

(g) Write the output of the following commands based on the given relation EMPLOYEE :

    (i) Select MIN (AGE) from EMPLOYEE

    (ii) Select sum (SALARY) from EMPLOYEE where GRADE = 'A'

    (iii) Select avg (SALARY) from EMPLOYEE where DEPT = "CIVIL"

    (iv) Select count (Distinct DEPT) from EMPLOYEE

Ans. (a) SELECT name FROM employee WHERE area = "SOUTH"

     (b) SELECT name, age FROM emplotee WHERE age > 40

     (c) SELECT * FROM employee WHERE salary BETWEEN (30000 and 40000)

     (d) SELECT * FROM employee ORDER BY dept

     (e) SELECT name FROM employee ORDER BY age DESC

     (f) INSERT INTO employee VALUES (7, "TARIK SINGH", 45000, "SOUTH",

         45, "C", "ELECT")

     (g) The output is provided after inserting the record of (f)

         (i) 29 (ii) 106000 (iii) 46000 (iv) 4 

48. Write SQL commands for the queries (c) to (h) and write the output of the SQL commands given in (i) based on a relation STUDENT shown below.

relation : student

(C) To show all infirmation about the students of History department.

(d) To list the names of female students who are in Hindi dept.

(e) To list names of all students with their date of admission in ascending order.

(f) To display student's Name, fee, age for male students only.

(g) To count the number of students with Age <23.

(h) To insert a new row in the STUDENT table with the following data :

    9,"Zaheer", 36, "Computer", 12/03/95, 230, "M"

(i) Give the output of following SQL statements :

    (i) Select COUNT (distinct dept) from STUDENT

    (ii) Select MAX (Age) from STUDENT where Sex = "F"

    (iii) Select AVG (Fee) from STUDENT where Dateofadm<{01/01/98}

    (iv) Select SUM (Fee) from STUDENT where Dateofadm<{01/01/98}

Ans. (a) SELECT * FROM student WHERE dept = "History"

     (b) SELECT name FROM student WHERE sex = "F" and DEPT = "Hindi"

     (c) SELECT name, dateofadm FROM student ORDER BY dateofadm

     (d) SELECT name, fee, age FROM student WHERE sex = "M"

     (e) SELECT count (*) FROM student WHERE age < 23

     (f) INSERT INTO student VALUES (9, "Zaheer", 36, "Computer", CTOD

         ("12/03/95"), 230, "M")

     (g) The output is provided after inserting the record of (f)

         (i) 3 (ii) 25 (iii) 218.33 (iv) 1310