What are DDL and DML?

Give the SQL statement to create a table STUDENT with Roll Number, Name, Age and Marks.

Create a table named PROGRAMMERS with the following structure:

i) Display the name of the programmer, which has the highest salary.
ii) Update the salary of all programmer by 2000 whose name start with letter 'R'.

Explain the concept of candidate keys with the help of an appropriate example.

Explain the concept of cartesian product between two tables, with the help of an appropriate example.

What is primary and alternate key in a database? Give a suitable example to explain each.

What do you understand by primary key? Give a suitable example of the primary key from a table containing some meaningful data.

What do you understand by normalization?

Consider the following relations
Teach (Name, Address, Course)
Give an expression in the relational algebra for each of the following:
(i) Print all the information about teachers who are teaching the 'DBMS' course.
(ii) Print the names and addresses of those teachers who teach 'computer'.
(iii) List all the teachers who live in Mumbai.

Observe the following table carefully and write the names of the most appropriate columns, which can be considered as (i) candidate keys and (ii) primary key.
Table: Product

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;

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;

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 to BASIC*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/7/98',35,"M"
(e) Give the output of the following SQL statements 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 DATEOFAPP>'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 E.INCHARGE.I
WHERE E.DEPARTMENT=I.DEPT;