Website for Online Contents Class XII CS

The students of Class XII and XI CS can visit the site for CS contents . The site is

www.kvsecontents.in

PPT on Classes and Objects

PPT on Inheritance

PPT On file Handling

PPT on Constructor and Destructor

Assignment SQL

ASSIGNMENT    DATABASE CONCEPTS

1                                                          Employees                                                                      EmpSalary

Empid	Firstname	Lastname	Address	City
010	Ravi	Kumar	Raj nagar	GZB
05	Harry	Waltor	Gandhi nagar	GZB
152	Sam	Tones	33 Elm St.	Paris
215	Sarah	Ackerman	440 U.S. 110	Upton
244	Manila	Sengupta	24 Friends street	New Delhi
300	Robert	Samuel	9 Fifth Cross	Washington
335	Ritu	Tondon	Shastri Nagar	GZB
400	Rachel	Lee	121 Harrison St.	New York
441	Peter	Thompson	11 Red Road	Paris

Empid	Salary	Benefits	Designation
010	75000	15000	Manager
105	65000	15000	Manager
152	80000	25000	Director
215	75000	12500	Manager
244	50000	12000	Clerk
300	45000	10000	Clerk
335	40000	10000	Clerk
400	32000	7500	Salesman
441	28000	7500	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)             To display the maximum salary among managers and clerks from the table Empsalary.

Give the Output of following SQL commands:                                                     

(i)                 Select firstname,salary from employees ,empsalary where designation = ‘Salesman’ and Employees.empid=Empsalary.empid;

(ii)               Select count(distinct designation) from empsalary;

(iii)             Select designation, sum(salary) from empsalary group by designation having count(*) >2;

(iv)             Select sum(benefits) from empsalary where designation =’Clerk’;

 2.        Consider the following tables Employee and salary. Write SQL commands for the statements (i) to (iv) and give   outputs for SQL queries (v) to (viii)

Table : Employee                                                                              Table : Salary

Eid	Name	Deptid	Qualification	Sex
1	Deepali Gupta	101	MCA	F
2	Rajat Tyagi	101	BCA	M
3	Hari Mohan	102	B.A	M
4	Harry	102	M.A	M
5	Sumit Mittal	103	B.Tech	M
6	Jyoti	101	M.Tech	F

Eid	Basic	DA	HRA	Bonus
1	6000	2000	2300	200
2	2000	300	300	30
3	1000	300	300	40
4	1500	390	490	30
5	8000	900	900	80
6	10000	300	490	89

                                                                       

(i)                 To d(i) Display  the frequency of employees department wise.

(ii)               To list the names of those employees only whose name starts with ‘H’

(iii)             To add a new column in salary table . the column name is total_sal.

(iv)             To store the corresponding values in the total_sal column.

(v)               Select name from employee where eid=(select eid from salary where basic= (select max(basic) from salary));

(vi)             select max(basic) from salary where bonus >40;

(vii)           Select count(*) from employee group by sex;

(viii)         select Distinct deptid from Employee;

3. Define the following-    Primary Key  , Candidate key , Alternate key  ,  Foreign key, Tuple , Attribute, Degree , Cardinality, DDL , DML, Domain , View                

4. What will be the degree and cardinality of the table produced by Cartesian product of the two tables given in  the

question no 2  ?

 5. Consider the following tables Employee and salary. Write SQL commands for the statements (i) to (iv) and give   outputs for SQL queries (v) to (viii)

TABLE: EMPLOYEE                                                                        TABLE: SALARIES

EMPID	EMPNAME	SECTION	SEX	EXPERIENC
101	Sabeena	PRODUCTION	F	10
102	Siva	ACCOUNTS	M	15
103	Ayaz	INSPECTION	M	10
104	Gayathri	PRODUCTION	F	8
105	Poorna	ACCOUNTS	F	9
106	Giridhar	INSPECTION	M	10
107	Sindhu	PRODUCTION	F	13
108	Suganya	ACCOUNTS	F	11
109	Hemanth	PRODUCTION	M	15
110	Suprakya	ACCOUNTS	F	7

EMPID	SALARY	INCENTIVE	ALLOWANCE
101	18000	1800	1325
103	23150	3100	1121
105	13120	2300	1240
106	12550	1250	1005
107	11145	1100	1290
109	18900	1990	1200
110	21400	2100	1312

          

i.                    Display EMPNAME of all staff who are in “ACCOUNTS” having more than 10 years experience from the table EMPLOYEE.

ii.                  Display the average PAY of all staff working in “INSPECTION” department using the tables EMPLOYEE and SALARIES. The total pay is calculated as PAY= SALARY +INCENTIVE+ ALLOWANCE.

iii.                Display the minimum INCENTIVE of female staff.

iv.                Display the highest ALLOWANCE among all male staff.

v.                  SELECT count (*) from EMPLOYEE where SEX = “F”.

vi.                SELECT EMPNAME, SECTION, ALLOWANCE from EMPLOYEE, SALARIES where SECTION = “ACCOUNTS” and EMPLOYEE.EMPID = SALARIES.EMPID.

vii.              SELECT EMPNAME from EMPLOYEE where SECTION= INSPECTION and SEX=’M’

viii.            Select  count (distinct  SECTION) from EMPLOYEE

6. Consider the following table and answer the questions-

S_I	Firstname	Lastname	City
010	Ravi	Kumar	GZB
053	Harry	Waltor	GZB
152	Sam	Tones	Paris
215	Sarah	Ackerman	Upton

1)      Identify the Primary key/keys in this table.

2)      What will be the domain of the column city.

7.         (a)        What do you understand by Degree and Cardinality of a table?             2

(b) Consider the following tables ACTIVITY and COACH. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii)   6

      Table: ACTIVITY

ACode	ActivityName	ParticipantsNum	PrizeMoney	ScheduleDate
1001	Relay 100x4	16	10000	23-Jan-2004
1002	High jump	10	12000	12-Dec-2003
1003	Shot Put	12	8000	14-Feb-2004
1005	Long Jump	12	9000	01-Jan-2004
1008	Discuss Throw	10	15000	19-Mar-2004

     

      Table: COACH

PCode	Name	ACode
1	Ahmad Hussain	1001
2	Ravinder	1008
3	Janila	1001
4	Naaz	1003

      (i)         To display the name of all activities with their Acodes in descending order.

(ii) To display sum of PrizeMoney for each of the Number of participants groupings (as shown in column ParticipantsNum 10,12,16)

     

(iii)       To display the coach’s name and ACodes in ascending order of ACode from the table COACH

(iv) To display the content of the GAMES table whose ScheduleDate earliar than 01/01/2004 in ascending order of ParticipantNum.

     

(i)     SELECT COUNT(DISTINCT ParticipantsNum) FROM ACTIVITY;

                             

                        (vi)SELECT MAX(ScheduleDate),MIN(ScheduleDate) FROM ACTIVITY;

           

                        (vii)      SELECT SUM(PrizeMoney) FROM ACTIVITY;

                                   

                        (viii) SELECT DISTINCT ParticipantNum FROM COACH;

                                               

8          (a)        What do you understand by Primary Key & Candidate Keys?               2

(b) Consider the following tables GAMES and PLAYER. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii)               6

      Table: GAMES

GCode	GameName	Number	PrizeMoney	ScheduleDate
101	Carom Board	2	5000	23-Jan-2004
102	Badminton	2	12000	12-Dec-2003
103	Table Tennis	4	8000	14-Feb-2004
105	Chess	2	9000	01-Jan-2004
108	Lawn Tennis	4	25000	19-Mar-2004

     

      Table: PLAYER

PCode	Name	Gcode
1	Nabi Ahmad	101
2	Ravi Sahai	108
3	Jatin	101
4	Nazneen	103

      (i)         To display the name of all Games with their Gcodes

(ii)  To display details of those games which are having PrizeMoney more than 7000.

     

(iii) To display the content of the GAMES table in ascending order of ScheduleDate.

(iv) To display sum of PrizeMoney for each of the Number of participation groupings (as shown in column Number 2 or 4)

(ii)   SELECT COUNT(DISTINCT Number) FROM GAMES;

                       

                        (vi)SELECT MAX(ScheduleDate),MIN(ScheduleDate) FROM GAMES;

                                   

                        (vii)      SELECT SUM(PrizeMoney) FROM GAMES;

                       

                        (viii) SELECT DISTINCT Gcode FROM PLAYER;