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%’;

Question. Raju wants to change the schema of the table “ STUDENT ” which already exists in the database. Which command he will use.
Answer: He will use the ALTER TABLE command.

Question. 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.
Answer: A CHECK constraint (specifically restricting the date of birth to be less than or equal to '30-Mar-2007', such as CHECK (DOB <= '2007-03-30')) might be specified on the date of birth column when the table was created.

Question. What is the purpose of the following query.
SELECT COUNT(*) FROM ELECTION GROUP BY city.
Answer: The purpose of this query is to find and display the number of records (such as voters or candidates) in the ELECTION table for each city, grouping the results by the city column.

Question. What is the role of UNIQUE constraint? How is PRIMARY KEY constraint differ from UNIQUE key constraint.
Answer: The role of the UNIQUE constraint is to ensure that all values in a column or set of columns are distinct (no duplicate values are allowed in that column).

The differences between a PRIMARY KEY constraint and a UNIQUE key constraint are:
1. A table can have only one PRIMARY KEY, whereas it can have multiple UNIQUE key constraints.
2. A PRIMARY KEY column cannot contain NULL values, whereas a UNIQUE key column can contain NULL values (depending on the database system, usually one or more NULL values are allowed).

Question. What is the significant of ORDER BY command in SQL query.
Answer: The ORDER BY command is used to sort the result set of a query in ascending (ASC) or descending (DESC) order based on one or more columns. By default, the sorting is done in ascending order.

ActivityName and ParticipantNo of table ACTIVITY given below:

Question. Based on this information, find the output of the query:
SELECT COUNT( DISTINCT ParticipantNo) FROM ACTIVITY;
Answer:

Question. Based on this information, find the output of the query:
SELECT ActivityName FROM ACTIVITY WHERE ActivityName LIKE ‘%p’;
Answer:

Question. 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?
Answer: If 3 more columns are added to the table:
- Cardinality (number of rows) remains unchanged, which is 2.
- Degree (number of columns) becomes \( 4 + 3 = 7 \).

Question. Write an SQL query to create following table ‘ Supplier’ with following descriptions.

Table : Supplier

Answer:

CREATE TABLE Supplier (
    Supp_id Varchar(5) PRIMARY KEY,
    Supp_Name Varchar(20) NOT NULL,
    City Varchar(20),
    Order_No Integer(10),
    Supp_Amount Decimal(6,2) CHECK (Supp_Amount >= 10000),
    FOREIGN KEY (Order_No) REFERENCES `Order`(Order_No)
);

In database there are two tables “Student” and “Stream” as below :

Table: Student

Table : Stream

Question. What is the name of primary key in both tables?
Answer:
- In the Student table, the primary key is Sid.
- In the Stream table, the primary key is StrId.

Question. What is the cardinality and degree of stream table?
Answer: For the Stream table:
- Cardinality (number of rows) = 4
- Degree (number of columns) = 2

Consider the following two tables STUDENT and LIBRARY as follows.

Table : STUDENT

Table : LIBRARY

Question. Write a SQL query to display student name,stream book issue and date of issue for each student.
Answer:

SELECT STUDENT.St_Name, STUDENT.Stream, STUDENT.Book_Issue, LIBRARY.Issue_Date 
FROM STUDENT, LIBRARY 
WHERE STUDENT.Book_Issue = LIBRARY.BookNo;

Question. Write a SQL query to display totalNumber of students in each stream.
Answer:

SELECT Stream, COUNT(*) AS totalNumber 
FROM STUDENT 
GROUP BY Stream;

Question. Find the output for the query:
SELECT St_Id,St_Name,Stream,Title,Issue_date
FROM STUDENT,LIBRARY WHERE STUDENT.Book_Issue=LIBRARY.BookNo
ORDER BY St_Name.
Answer:

Question. What is Back end? Explain its functions.
Answer: The back end refers to the database server or application tier that manages, stores, and processes data, executing operations behind the scenes. It remains hidden from the end user.

Functions of Back end:
1. Data Management: Efficient storage, retrieval, and updates of structured or unstructured data.
2. Data Integrity & Concurrency Control: Ensures transactional safety (ACID properties), enforces check and key constraints, and manages simultaneous access by multiple users.
3. Data Security: Manages authentication, authorization, and roles to secure sensitive data from unauthorized access.

Question. How has our society benefited from e-commerce? Mention any two points?
Answer:
1. Convenience and Flexibility: Consumers can buy products and services 24/7 from any location without needing to visit physical stores.
2. Greater Choice and Comparison: Users have access to a global marketplace, allowing them to compare products, reviews, and prices across different brands instantly.

Question. What controls would you suggest for following types of inputs?
(i) To display some information
(ii) Enter password
(iii) To choose more than one subject from a set of subjects.
(iv) To enter multiline text
Answer:
(i) To display some information: Label (e.g., JLabel)
(ii) Enter password: Password Field (e.g., JPasswordField)
(iii) To choose more than one subject: Check Box (e.g., JCheckBox)
(iv) To enter multiline text: Text Area (e.g., JTextArea)