What are DDL and DML?

Write a query on the SALESPEOPLE table, whose output will exclude all salespeople with a rating >=100, unless they are located in Delhi.

What is the difference between degree and cardinality of a table? What is the degree and cardinality of the following table?

Differentiate between the terms degree and cardinality in context of RDBMS. 

What do you mean by the union and cartesian product operation in relational algebra?

Give a suitable example of a table with sample data and illustrate primary and alternate keys in it.

What is the need of normalization?

Define the following terms:
(i) First Normal Form
(ii) Second Normal Form
(iii) Third Normal Form

Answer the questions (a) and (b) on the basis of the following tables SHOPPE and ACCESSORIES.
                                             TABLE SHOPPE

                                         TABLE ACCESSORIES

(a) Write the SQL queries:

(i) To display Name and price of all the Accessories in ascending order of their price.
(ii) To display id and Sname of all Shoppe located in Nehru place.
(iii) To display Minimum and Maximum price of each Name of Accessories.
(iv) To display Name,Price of all Accessories and their respective SName,where they are available.
(b) Write the output of the following SQL commands;
(i) SELECT DISTINCT NAME FROM ACCESSORIES WHERE PRICE>=5000;
(ii)SELECT AREA.COUNT (*) FROM SHOPPE GROUP BY AREA;
(iii)SELECT COUNT (DISTINCT AREA) FROM SHOPPE;
(iv)SELECT NAME,PRICE*0.05 DISCOUNT FROM ACCESSORIES WHERESNO IN ('S02','S03');

Write SQL queries for (a) to (f) and write the output for the SQL queries mentioned in (g) parts (i) to (iv) on the basis of tables ITEMS and TRADERS.
TABLE: ITEMS

TABLE: TRADERS 

(a) To display the details of all the items in ascending order of item names(i.e. INAME).
(b) To display item name and price of all those items whose price is in the range of 10000 nd 22000(both values inclusive)
(c) To display the number of items, which are traded by each trader. The expected output of this query should be:

(d) To display the Price, item name(ie.name) and quantity(ie.Qty) of those items, which have quantity more than 150.
(e) To display the names of those traders, who are either from DELHI or from MUMBAI.
(f) To display the name of the companies and the name of the items in descending order of company names.
(g) Obtain the outputs of the following SQL queries based on the data given in the tables ITEMS and TRADERS above.
(i) SELECT MAX(Price), MIN(Price) FROM ITEMS;
(ii) SELECT Price * Qty AMOUNT
    FROM ITEMS WHERE Code=1004;
(iii) SELECT DISTINCT Tcode FROM ITEMS;
(iv) SELECT IName, TName
    FROM ITEMS I, TRADERS T
   WHERE I.Code=T.TCode AND Qty<100;

Write SQL queries for (a) to (f) and write the outputs for (g) parts (i) to (iv) on the basis of tables APPLICANTS and COURSES.
TABLE: APPLICANTS

TABLE: COURSES 

(a) To display NAME, FEE, Gender, JOINYEAR about the APPLICANTS, who have joined 
before 2010.
(b) To display the names of applicants, who are paying FEE more than 30000.
(c) To display the names of all applicants in ascending order of their joinyear.
(d) To display the year and the total number of applicants joined in each year
from the table APPLICANTS>
(e) To display the C_ID and the number of applicants registered in the course 
from the APPLICANTS table.
(f) To display the applicant's name with their respective course's name from the 
tables APPLICANTS and COURSES.
(g) Give the output of the following SQL statements:
(i) SELECT NAME,JOINYEAR FROM APPLICANTS WHERE GENDER='F' AND C_ID='A02';
(ii) SELECT MIN(JOINYEAR) FROM APPLICANTS WHERE GENDER='M';
(iii) SELECT AVG(FEE) FROM APPLICANTS WHERE C_ID='A01' OR C_ID='A05';
(iv) SELECT SUM(FEE), C_ID FROM APPLICANTS GROUP BY C_ID HAVING COUNT(*)=2;

Consider the following tables CUSTOMER and ONLINESHOP.Write SQL commands for the statements (a) to (d) and give outputs for SQL queries(e) to (h).
TABLE: CUSTOMER

TABLE: ONLINESHOP

(a) To display CNAME, AREA of all female customers from CUSTOMER table.
(b) To display the details of all the CUSTOMERS in ascending order of CNAME within SID.
(c) To display the total number of customers for each AREA from CUSTOMER table.
(d) to display CNAME and corres[ponding SHOP from CUSTOMER table and ONLINESHOP table.
(e) SELECT COUNT(*), GENDER FROM CUSTOMER GROUP BY GENDER;
(f) SELECT COUNT(*) FROM ONLINESHOP;
(g) SELECT CNAME FROM CUSTOMER WHERE CNAME LIKE 'L%';
(h) SELECT DISTINCT AREA FROM CUSTOMER;

Consider the following tables CARDEN and CUSTOMER and answer(a) and (b) parts of this questions:
TABLE: CARDEN 

TABLE: CUSTOMER

(a) Write SQL commands for the following statements:
(i) To display the name of all the SILVER colored cars.
(ii) To display name of CAR,MAKE and sitting Capacity of cars in descending order of their sitting Capacity.
(iii) To display the higher Charges at which a vehicle can be hired from CARDEN.
(iv) Todisplay the customer name and the corresponding name of the cars hired by them.
(b) Give the output of the following SQL queries:
(i) SELECT COUNT(DISTINCT Make) FROM CARDEN;
(ii) SELECT MAX(Carges),MIN (Charges) FROM CARDEN;
(iii) SELECT COUNT(*) Make FROM CARDEN;
(iv) SELECT CarName FROM CARDEN WHERE Capacity=4;

Consider the following tables.Write SQL commands for the statements (a) to (d) and give outputs for SQL queries (e) to (h)
TABLE: SENDER 

TABLE:RECEIPIENT 

(a) To display the names of all Sender from Mumbai.
(b) To display the RecID, Sender Name, Sender Address, Rec Name, Rec Address for every Recipient.
(c) To display Recipient details in ascending order of RecName.
(d) To display number of Recipients from each city.
(e) SELECT DISTINCT SenderCity FROM SENDER;
(f) SELECT A.SenderName, B.Recname FROM SENDER A, RECIPIENT B
    WHERE A.Sender ID=B.SenderID AND B>RecCity='Mumbai';
(g) SELECT Recname, RecAddress FROM RECIPIENT
      WHERE RecCity NOT IN('Mumbai','Kolkata');
(h) SELECT RecID, RecNam FROM RECIPIENT
     WHERE SenderID='MU02' OR SenderID='ND50';

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

(a) List the name of all students, who have taken stream as COMPUTER.
(b) To count the number of female students.
(c) To display the number of students stream wise.
(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 sorted 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;

Given the following family relation. Write SQL commands for questions (a) to (f) and output of (g) based on the table FAMILY
TABLE: FAMILY

(a) To select all the information of family, whose Occupation is service.
(b) To list the name of family, where female members are more than 3.
(c) To list all names of family with income in ascending order.
(d) To display family's name, male members and occupation of business family.
(e) To count the number of family, whose income is less than 10,000.
(f) To insert a new record in the FAMILY table with the following data
   9,"D'SOUZA",2,1,15000,"Service"
(g) Give the output of the following SQL commands
(i) SELECT MIN(DISTINCT Income) FROM FAMILY;
(ii) SELECT MIN(FemaleMembers) FROM FAMILY WHERE Occupation='Mixed';
(iii) SELECT SUM(Income) FROM FAMILY WHERE Occupation='Service';
(iv) SELECT AVG(Income) FROM FAMILY;