Structured Query Language

Structured Query Language Class Assignment 1.
Write the SQL query commands based on following table
Table : Book Book_id Book name C0001 Fast Cook F0001 T0001 T0002 F0002
Author_name Lata Kapoor William The Tears Hopkins Brain & My First c++ Brooke C++ Brain A.W. works Rossaine Thunderbolts Anna Roberts
Publisher EPB First Publi. FPB TDH First Publ.
Price 355 650 350 350 750
Type Quantity Cookery 5 Fiction Text Text Fiction 20 10 15 50
Table : issued Book_Id T0001 C0001 F0001
Quantity Issued 4 5 2
Write SQL query for (a) to (f) (a) To show book name, Author name and price of books of First Pub. Publisher (b) To list the names from books of text type (c) To Display the names and price from books in ascending order of their prices. (d) To increase the price of all books of EPB publishers by 50. (e) To display the Book_Id, Book_name and quantity issued for all books which have been issued (f) To insert a new row in the table issued having the following data. F0003, 1 (g) Give the output of the following i. Select Count(*) from Books ii. Select Max(Price) from books where quantity >=15 iii. Select book_name, author_name from books where publishers=first publ. iv. Select count(distinct publishers) from books where Price>=400
2. TABLE: GRADUATE
S.NO
1 2 3 4 5 6 7 8 9 10
NAME
KARAN DIWAKAR DIVYA REKHA ARJUN SABINA JOHN ROBERT RUBINA VIKAS
STIPEND
400 450 300 350 500 400 250 450 500 400
SUBJECT
PHYSICS COMP. Sc. CHEMISTRY PHYSICS MATHS CEHMISTRY PHYSICS MATHS COMP. Sc. MATHS
AVERAGE
68 68 62 63 70 55 64 68 62 57
DIV.
I I I I I II I I I II
(a) (b) (c.) (d) (e)
List the names of those students who have obtained DIV I sorted by NAME. Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend received in a year assuming that the STIPEND is paid every month. To count the number of students who are either PHYSICS or COMPUTER SC graduates. To insert a new row in the GRADUATE table: 11,KAJOL, 300, COMP. SC., 75, 1 Give the output of following sql statement 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 where AVERAGE>=65;
(iv) Select COUNT(distinct SUBDJECT) from GRADUATE; Assume that there is one more table GUIDE in the database as shown below:
Table: GUIDE
MAINAREA
PHYSICS COMPUTER SC CHEMISTRY MATHEMATICS
ADVISOR
VINOD ALOK RAJAN MAHESH
(f)
What will be the output of the following query: SELECT NAME, ADVISOR FROM GRADUATE,GUIDE MAINAREA; 3. Q.19. Table: Employees Empid Firstname Lastname Address
010 105 152 215 244 300 335 400 441 Ravi Harry Sam Sarah Manila Robert Ritu Rachel Peter Kumar Waltor Tones Ackerman Sengupta Samuel Tondon Lee Thompson Raj nagar Gandhi nagar 33 Elm St. 440 U.S. 110 24Friends street 9 Fifth Cross Shastri Nagar 121 Harrison St. 11 Red Road
WHERE
SUBJECT=
City
GZB GZB Paris Upton New Delhi Washington GZB New York Paris
Table: EmpSalary Empid

010 105 152 215 244 300 335 400 441
Salary
75000 65000 80000 75000 50000 45000 40000 32000 28000
Benefits
15000 15000 25000 12500 12000 10000 10000 7500 7500
Designation
Manager Manager Director Manager Clerk Clerk Clerk Salesman salesman
Write the SQL commands for the following : (i) To show firstname,lastname,address and city of all employees living in paris (ii) To display the content of Employees table in descending order of Firstname. (iii) To display the firstname,lastname and total salary of all managers from the tables Employee and empsalary , where total salary is calculated as salary+benefits. (iv) (v) To display the maximum salary among managers and clerks from the table Empsalary. Give the Output of following SQL commands:
(i) (ii) (iii) (iv)
Select firstname,salary from employees ,empsalary where designation = Salesman and Employees.empid=Empsalary.empid; Select count(distinct designation) from empsalary; Select designation, sum(salary) from empsalary group by designation having count(*) >2; Select sum(benefits) from empsalary where designation =Clerk;
4. What is foreign Key? What is its purpose? 5. Define the terms Tuple, Attribute, Primary Key, Candidate key, Alternate Key ? 6. What do you understand by the terms Cardinality and Degree of the table? 7. What are DDL and DML? 8. What is the difference between Where and Having Clause ? 9. What do you understand by constraints? 10.What are group/Aggregrate Functions? 11. Explain Selection and Projection with example 12. List advantages of DBMS? 13. What do you mean by RDBMS? Name any two RDBMS packages? 2

Structured Query Language

Uploaded by

Copyright:

Available Formats

Structured Query Language

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Structured Query Language

Uploaded by

Copyright:

Available Formats

Structured Query Language Class Assignment 1.

Write the SQL query commands based on following table

Publisher EPB First Publi. FPB TDH First Publ.

Price 355 650 350 350 750

Type Quantity Cookery 5 Fiction Text Text Fiction 20 10 15 50

Table : issued Book_Id T0001 C0001 F0001

(a) (b) (c.) (d) (e)

Table: EmpSalary Empid

(i) (ii) (iii) (iv)

You might also like