CBSE Class 12 Informatics Practices MySQL Worksheet

Q1. a) Raju wants to change the schema of the table “ STUDENT ” which already exists in the database. Which command he will use.

1 b) Neha is not able to enter the details of students born after ‘ 30-Mar-2007’. Which constraint might be specified when the table was created.

1 c) What is the purpose of the following query. 1 SELECT COUNT(*) FROM ELECTION GROUP BY city.

d) What is the role of UNIQUE constraint? How is PRIMARY KEY constraint differ from UNIQUE key constraint.

2 e) What is the significant of ORDER BY command in SQL query.

2 f) ActivityName and ParticipantNo of table ACTIVITY given below 2 Activity Name ParticipantNo Relay 100X 4 16 High Jump 10 Shot Put 12 Long Jump 12 Discuss Throw 10 Based on this information, find the output of the following queries.

a) SELECT COUNT( DISTINCT ParticipantNo) FROM ACTIVITY;

b) SELECT ActivityName FROM ACTIVITY WHERE ActivityName LIKE ‘%p’;'

g) A table Employee in a database has a 4 columns and 2 rows in it. What is cardinality and degree if 3 more columns are added in the table? 1 Q2. A)Write an SQL query to create following table ‘ Supplier’ with following descriptions. 2 Table : Supplier Field Type Constraint Supp_id Varchar(5) Primary Key Supp_Name Varchar(20) Not Null City Varchar(20) Order_No Integer(10) Foreign key with reference to Order_No column in Order table Supp_Amount Decimal(6,2) Minimum 10000 b) In database there are two tables “Student” and “Stream” as below : Table: Student Sid Sname age Strid 1 Roondy 10 1 2 Joseph 12 1 3 Trinchu 14 2 4 Prithlon 11 3 5 Ahol 10 2 

Q. Create the following tables with the following constraints and values.
Table : TicketDetails

Q. Add the following values to the above table

Table : AgentDetails

Add the following values to the above table

Q. To display Tcode,Name and Aname of all the records where the number of tickets sold is more than 5.

Q. To list out Name, Tickets,A_code and Aname for all the Agent Details.

Q. To list out all the Acode, Aname and corresponding Tcode where Aname ends with ‘k’.

Q. To Total number of tickets booked by each customer.

Q. To display total number of tickets booked by agent “Mr. Ayush”.

Q1. Create the Table Emp with the following constraints:

Q2. Add the following records to the above table “Emp” and Write commands in SQL

1. To display list of all employees below 25 years old.

2. To list names and respective salaries in descending order of salary.

3. To count the number of employees with names starting with ‘K’

4. To list names and addresses of those persons who have ‘Delhi’ in their address.

5. To list out all the employees whose salary is in between 70000 and 80000.

6. Count number of employees based on their age group.

7. To list out all the employee names whose phone number starts with ‘99’.

8. To display all the details of employee whose salary is above 85000.

9. To display all the employee names whose name contains the character ‘a’ anywhere.

Find the output:

1.select name from emp where sal=select max(sal) from emp;

2. select length(name) from emp where salary >82000;

3. select 9* 4 from emp where id>6;

4. select Left(address,4) from emp where id <3;

5. select mid(name,2,3) from emp where address like ‘%delhi%’;