Define the - KV KHAGARIA DIGITAL LIBRARY



CBSE Ques. No. Chapter / Topic Marks Distribution of marks question wise 5 Database Concepts SQL0206One question of database concepts. 04 SQL queriesof 1 mark each and 04 questions from output carrying ? mark eachQUESTIONS FOR LATE BLOOMERS:Q.2. What do you understand by the terms primary key and degree of a relation in relational data base??Ans: Primary Key: A primary key is a set of one or more attributes that can??????? ?uniquely identify tuples within the relations. The number of attributes in a relation is called Degree of arelation in relational data base.?? What do you understand by the candidate key and cardinality of a relation in relational data base?Candidate Key: All attribute combinations inside a relation that can serve as primary key(uniquely identifies a row in a relation) are Candidate Keys as they are candidates for the primary key position. The number of rows in a relation is known as cardinality of a relation.Define the terms Tuple and AttributeAns: Tuples: The rows of tables (relations) are generally referred to as tuples.Attribute: The columns of tables are generally referred to as attribute. Q.3. What do you understand by the terms Cardinality and Degree of the table?AnsDegree: The number of attributes in a relation determines the degree of a relation. A relation having 3 attributes is said to be a relation of degree 3. Cardinality: The number of rows in a relation is known as Cardinality.Q.4. What is the main function of DBA.Ans: The DBA must be a manager, more than a technician-seeking to meet the needs of people who use the data. Since many user may share the same data resource, the DBA must be prepared to meet the need and objective. Q5. What is foreign Key? What is its purpose?Ans: A non key attribute, whose value are derived from the primary key of some other table, is known as foreign key in the current table. The table in which this non-key attribute i.e. foreign key attribute exists, is called a foreign table. Q.6. What is the main function of DBA.Ans: The DBA must be a manager, more than a technician-seeking to meet the needs of people who use the data. Since many user may share the same data resource, the DBA must be prepared to meet the need and objective. Q.7 Write a query on the customers table whose output will exclude all customers with a rating <=100, unless they are located in Shimla.Ans. SELECT * FROM customers WHERE rating >100 OR city =’Shimla’ ;Q.8. Write a query that selects all orders except those zeros or NULLs in the amount field. Ans. SELECT * FROM Orders WHERE amt < >0 AND (amt IS NOT NULL) ;Q.9. Write a query that lists customers in descending order of rating. Output the rating field first, followed by the customer’s name and number. Ans. SELECT rating, cust-name, cust-num FROM customers ORDER BY rating DESC ;Q.10. Write a command that puts the following values, in their given order, into the salesman table:cust-name-Manisha, city-Manali, comm.- NULL, cust-num-1901. Ans. INSERT INTO salesman (city, cust-name, comm.,cust-num) VALUES(‘Manisha’,NULL,1901) ;Q.11.What are DDL and DML?Ans:- The DDL provides statements for the creation and deletion of tables and indexes. The DML provides statements to enter, update, delete data and perform complex queries on these tables. Q.12. What is the difference between Where and Having Clause ?Ans: The HAVING clause places the condition on group but WHERE clause places the condition on individual rows Q.13. What do you understand by constraints ?Ans: Constraints are used to enforce rules at table level when ever row is inserted, updated/deleted from table. Constraints can be defined to one of the Two level. Column Level: Reference to a single column. can be defined any type of integrity. Table Level: References one or more columns and is defined separately from definition of the columns in the table. Q.14. Write some features of SQL? Ans: Recovery ad Concurrency:- Concurrency is concerned with the manner in which multiple user operate upon the Database. Security: The Security can be maintained by view mechanism. Integrity Constraints-> Integrity constraints are enforced by the system. Q.15. Write various database objects available in SQL?Ans: Table: A Table is used to store Data View: A view is the temporary table created using Original table. Sequence: Sequences are used to generate Primary key value. Index: They are used to improve queries. Synonym: They give alternative names to objects. Q.16. Write the rules to name an objects? Ans :The maximum length must be 30 character long. The Object name should not contain quotation mark. The name must start with letter. The use of $ and # is discouraged in the object name. A name must not be a reserved name. Q.17. What are group Functions Ans: The aggregate functions are group functions. They return result based on groups of rows. The group functions are AVG(), COUNT(), MAX(), MI N(), SUM()Q.18. What are column alias? Ans: In many cases heading table may not be descriptive and hence it difficult to understand. In such case we use columns alias It will change column heading with column alias. QUESTIONS FOR AVERAGE LEARNERSConsider the following tables Product and Client. Write SQL commands for the statement (i) to (iv) and give outputs for SQL queries (v) to (viii)Table:?PRODUCTP_IDProduct NameManufacturerPriceTP01TalcomPowderLAK40FW05Face WashABC45BS01Bath SoapABC55SH06ShampooXYZ120FW12Face WashXYZ95Table: CLIENTC_IDClient NameCityP_ID01TalcomPowderDelhiFW0506Face WashMumbaiBS0112Bath SoapDelhiSH0615ShampooDelhiFW1216Face WashBangloreTP01(i)?To display the details of those Clients whose city is Delhi.Ans: Select all from Client where City=”Delhi”(ii)?To display the details of Products whose Price is in the range of 50 to 100(Both values included).Ans:?Select all from product where Price between 50 and 100(iii)?To display the ClientName, City from table Client, and ProductName and Price from table Product, with their corresponding matching P_ID.Ans:?Select ClientName,City,ProductName, Price from Product,Client where Product.P_ID=Client.P_ID.(iv)?To increase the Price of all Products by 10Ans:?Update Product Set Price=Price +10(v)?SELECT DISTINCT Address FROM Client.Ans:?( The above question may consist DISTINCT City. If it is DISTINCT City, the following is the answer)City-----DelhiMumbaiBangalore(vi)?SELECT Manufacturer, MAX(Price), Min(Price), Count(*) FROM Product GROUP BY Manufacturer;Ans:Manufacturer ???Max(Price)??? Min(Price)??? Count(*)???LAK ???????????????????? 40 ??????????????????40 ??????????????? 1???ABC ??????????????????? ?55???????????????????45 ???????????????2???XYZ????????????????????? 120??? ?????????????95 ??????????????? 2(vii)?SELECT ClientName, ManufacturerName FROM Product, Client WHERE Client.Prod_Id=Product.P_Id;Ans:ClientName??????????????? ManufacturerNameCosmetic Shop??????????? ABCTotal Health ?????????????????ABCLive Life ???????????????????????? XYZPretty Woman?????????????? XYZDreams???????????????????????? LAK(viii)?SELECT ProductName, Price * 4 FROM Product.ProductName????????? Price*4?Talcom Poweder ??????160?Face Wash ??????????????? 180?Bath Soap ??????????????????220?Shampoo ?????????????????? 480?Face Wash ??????????????? 380QUESTIONS FOR BRIGHT LEARNERS. What is Cartesian Product.Write the SQL query commands based on following tableTable : BookBook_idBook name Author_name PublisherPrice Type Quantity C0001Fast Cook Lata Kapoor EPB 355Cookery 5 F0001 The Tears William Hopkins First Publi. 650Fiction 20T0001 My First c++Brain & Brooke FPB 350Text10T0002C++ Brain works A.W. Rossaine TDH 350Text15F0002Thunderbolts Anna Roberts First Publ. 750Fiction 50 Table : issued Book_Id Quantity Issued T00014C0001 5F0001 2 Write SQL query for (a) to (f)To show book name, Author name and price of books of First Pub. Publisher To list the names from books of text type To Display the names and price from books in ascending order of their prices. To increase the price of all books of EPB publishers by 50. To display the Book_Id, Book_name and quantity issued for all books which have been issued To insert a new row in the table issued having the following data. ‘F0003’, 1 Give the output of the following Select Count(*) from Books Select Max(Price) from books where quantity >=15 Select book_name, author_name from books where publishers=’first publ.’Select count(distinct publishers) from books where Price>=400Ans:Select book_name, author_name , price from books where publisher=’First Publ’Select book_name from books where type=’Text’Select book_name, price from books Order by Price;Update books set price=price+50 where publishers=’EPB’Select a.book_id,a.book_name,b.quantity_issued from books a, issued b where a.book_id=b.book_idInsert into issued Values (‘F0003’,1); 5750Fast Cook Lata Kappor My First c++ Brain & Brooke 1Q.18. TABLE: GRADUATES.NONAMESTIPENDSUBJECTAVERAGEDIV.1KARAN400PHYSICS68I2DIWAKAR450COMP. Sc.68I3DIVYA300CHEMISTRY62I4REKHA350PHYSICS63I5ARJUN 500MATHS70I6SABINA400CEHMISTRY55II7JOHN250PHYSICS64I8ROBERT450MATHS68I9RUBINA500COMP. Sc.62I10VIKAS400MATHS57II(a) List the names of those students who have obtained DIV I sorted by NAME.(b)Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend received in a year assuming that the STIPEND 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, “COMP. SC.”, 75, 1 (e) Give the output of following sql statement based on table GRADUATE:Select MIN(AVERAGE) from GRADUATE where SUBJECT=”PHYSICS”;Select SUM(STIPEND) from GRADUATE WHERE div=2;Select AVG(STIPEND) from GRADUATE where AVERAGE>=65;Select COUNT(distinct SUBDJECT) from GRADUATE; Assume that there is one more table GUIDE in the database as shown below: Table: GUIDEMAINAREAADVISORPHYSICSVINODCOMPUTER SCALOKCHEMISTRYRAJANMATHEMATICSMAHESH (f) What will be the output of the following query:SELECT NAME, ADVISORFROM GRADUATE,GUIDE WHERE SUBJECT= MAINAREA;Ans:(a) SELECT NAME FROM GRADUATE WHERE DIV='I' ORDER BY NAME;(b)SELECT NAME, STIPEND, SUBJECT, STIPEND*12 STIPEND_YEAR FROM GRADUATE;(c)SELECT SUBJECT, COUNT(NAME) FROM GRADUATE GROUPBY (SUBJECT) HAVING SUBJECT='PHYSICS' OR SUBJECT='COMP. Sc.';(d)INSERT INTO GRADUATE VALUES(11,'KAJOL',300,'COMP. Sc.',75,1);(e)(i)MIN(AVERAGE)63(ii) SUM(STIPEND)800(iii) AVG(STIPEND)420(iv) COUNT(DISTINCTSUBJECT)4(f)SELECT NAME, ADVISOR FROM GRADUATE, GUIDE WHERE SUBJECT=MAINAREA;NAME ADVISORDIVYA RAJANSABINA RAJANKARAN VINODREKHA VINODJOHN VINODQ.19. Table: Employees EmpidFirstnameLastnameAddressCity010RaviKumarRaj nagarGZB105HarryWaltorGandhi nagarGZB152SamTones33 Elm St.Paris215SarahAckerman440 U.S. 110Upton244ManilaSengupta24Friends streetNew Delhi300RobertSamuel9 Fifth CrossWashington335RituTondonShastri NagarGZB400RachelLee121 Harrison St.New York441PeterThompson11 Red RoadParis Table: EmpSalaryEmpidSalaryBenefitsDesignation0107500015000Manager1056500015000Manager1528000025000Director2157500012500Manager2445000012000Clerk3004500010000Clerk3354000010000Clerk400320007500Salesman441280007500salesmanWrite the SQL commands for the following :To show firstname,lastname,address and city of all employees living in paris To display the content of Employees table in descending order of Firstname.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.To display the maximum salary among managers and clerks from the table Empsalary.Give the Output of following SQL commands: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’;Ans:(i) select firstname, lastname, address, city from employees where city='Paris';(ii)select * from employees order by firstname desc;(iii)select employees.firstname, employees.lastname, empsalary.salary + empsalary.benefits total_salary from employees, empsalary where employees.empid=empsalary.empid and designation='Manager';(iv)select max(salary) from empsalary where designation in('Manager','Clerk')(v)(i) select firstname, salary from employees, empsalary where designation='Salesman' and employees.empid=empsalary.empidFIRSTNAME SALARYRachel 32000Peter 28000(ii)select count(distinct designation) from empsalary;COUNT(DISTINCTDESIGNATION) 4(iii)select designation, sum(salary) from empsalary group by designation having count(*)>2;DESIGNATION SUM(SALARY)Clerk 135000Manager 215000(iv)select sum(benefits) from empsalary where designation='Clerk';SUM(BENEFITS) 32000Consider the following tables Item and Customer. Write SQL commands for the statement (i) to (iv) and give outputs for SQL queries (v) to (viii)Table:?ITEMC_IDItemNameManufacturerPricePC01Personal ComputerABC35000LC05LaptopABC55000PC03Personal ComputerXYZ32000PC06Personal ComputerCOMP37000LC03LaptopPQR57000Table:?CUSTOMERC_IDCustomerNameCityP_ID01N.RoyDelhiLC0306H.SinghMumbaiPC0312R.PandeyDelhiPC0615C.SharmaDelhiLC0316K.AgarwalhBanglorePC01(i)?To display the details of those Customers whose city is Delhi.Ans: Select all from Customer Where City=”Delhi”(ii)?To display the details of Item whose Price is in the range of 35000 to 55000 (Both values included).Ans:?Select all from Item Where Price>=35000 and Price <=55000(iii)?To display the CustomerName, City from table Customer, and ItemName and Price from table Item, with their corresponding matching I_ID.Ans:?Select CustomerName,City,ItemName, Price from Item,Customer where Item.I_ID=Customer.I_ID.(iv)?To increase the Price of all Items by 1000 in the table Item.Ans:?Update Item set Price=Price+1000(v)?SELECT DISTINCT City FROM Customer.Ans:?? ?City??????????? ?Delhi?????????????Mumbai??????????? ?Bangalore(vi)?SELECT ItemName, MAX(Price), Count(*) FROM Item GROUP BY ItemName;Ans:ItemName ? ? ? ? ? ? ? ? ? ? ?Max(Price) ? ?Count(*)Personal Computer????? ??37000?????????? ????3Laptop??????????????????????????????57000?????????? ? ??2(vii)?SELECT CustomerName, Manufacturer FROM Item, Customer WHERE Item.Item_Id=Customer.Item_Id;Ans:CustomerName ? ? ? ? ? ?Manufacturer NameN.Roy?? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??PQRH.Singh???????????????????????????????XYZR.Pandey?? ? ? ? ? ? ? ? ? ? ? ? ??COMPC.Sharma???????????????????????????PQRK.Agarwal???????????????????????????ABC(viii)?SELECT ItemName, Price * 100 FROM Item WHERE Manufacturer = ‘ABC’;Ans:ItemName ? ? ? ? ? ? ? ? ? ? ?Price*100Personal Computer?? ? ?3500000Laptop????????????????????????????5500000 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download