NCERT solutions for इनफार्मेशन प्रेकटिसेस [अंग्रेजी] कक्षा ११ chapter 8 - Introduction to Structured Query Language (SQL) [Latest edition]

Explore LibreOffice Base and compare it with MySQL.

What are the other data types supported in MySQL? Are there other variants of integer and float data type?

Can you think of an attribute for which fixed length string is suitable?

Which two constraints when applied together will produce a Primary Key constraint?

Type the statement show database;. Does it show the name of StudentAttendance database?

Can we have a CHAR or VARCHAR data type for contact number (mobile, landline)?

Create the other two relations GUARDIAN and ATTENDANCE as per data types given in Table, and view their structures. Don't add any constraint in the two tables.

Name foreign keys in table ATTENDANCE and STUDENT. Is there any foreign key in table GUARDIAN.

ATTENDANCE

STUDENT

GUARDIAN

Match the following clauses with their respective functions.

Choose the appropriate answer with respect to the following code snippet.

code snippet.
CREATE TABLE student(
name CHAR(30)
student_id INT,
gender CHAR(1),
PRIMARY KEY (student_id)
);

What will be the degree of student table?

Choose the appropriate answer with respect to the following code snippet.

code snippet.
CREATE TABLE student(
name CHAR(30)
student_id INT,
gender CHAR(1),
PRIMARY KEY (student_id)
);

What does ‘name’ represent in the above code snippet?

Choose the appropriate answer with respect to the following code snippet.

code snippet.
CREATE TABLE student(
name CHAR(30)
student_id INT,
gender CHAR(1),
PRIMARY KEY (student_id)
);

What is true about the following SQL statement?
SelecT * fROM student;

Choose the appropriate answer with respect to the following code snippet.

code snippet.
CREATE TABLE student(
name CHAR(30)
student_id INT,
gender CHAR(1),
PRIMARY KEY (student_id)
);

What will be the output of following query?
INSERT INTO student

VALUES (“Suhana”,109,’F’),
VALUES (“Rivaan”,102,’M’),
VALUES (“Atharv”,103,’M’),
VALUES (“Rishika”,105,’F’),
VALUES (“Garvit”,104,’M’),
VALUES (“Shaurya”,109,’M’);

Choose the appropriate answer with respect to the following code snippet.

code snippet.
CREATE TABLE student(
name CHAR(30)
student_id INT,
gender CHAR(1),
PRIMARY KEY (student_id)
);

In the following query how many rows will be deleted? 

DELETE student
WHERE student_id=109;

______ declares that an index in one table is related to that in another table.

The symbol Asterisk (*) in a select query retrieves ______.

Consider the following MOVIE table and write the SQL query based on it.

1. Retrieve movies information without mentioning their column names.
2. List business done by the movies showing only MovieID, MovieName and BusinessCost.
3. List the different categories of movies
4. Find the net profit of each movie showing its ID, Name and Net Profit.
   (Hint: Net Profit = BusinessCost – ProductionCost) Make sure that the new column name is labelled as NetProfit. Is this column now a part of the MOVIE relation. If no, then what name is coined for such columns? What can you say about the profit of a movie which has not yet released? Does your query result show profit as zero?
5. List all movies with ProductionCost greater than 80,000 and less than 1,25,000 showing ID, Name and ProductionCost.
6. List all movies which fall in the category of Comedy or Action.
7. List the movies which have not been released yet.

Suppose your school management has decided to conduct cricket matches between students of Class XI and Class XII. Students of each class are asked to join any one of the four teams – Team Titan, Team Rockers, Team Magnet, and Team Hurricane. During summer vacations, various matches will be conducted between these teams. Help your sports teacher to do the following:

1. Create a database “Sports”.
2. Create a table “TEAM” with the following considerations:
   i) It should have a column TeamID for storing an integer value between 1 to 9, which refers to the unique identification of a team.
   ii) Each TeamID should have its associated name (TeamName), which should be a string of length not less than 10 characters.
3. Using table level constraint, make TeamID the primary key.
4. Show the structure of the table TEAM using a SQL statement.
5. As per the preferences of the students four teams were formed as given below. Insert these four rows in the TEAM table:
   Row 1: (1, Team Titan)
   Row 2: (2, Team Rockers)
   Row 3: (3, Team Magnet)
   Row 3: (4, Team Hurricane)
6. Show the contents of the table TEAM using a DML statement.
7. Now create another table MATCH_DETAILS and insert data as shown below. Choose appropriate data types and constraints for each attribute.
8. Use the foreign key constraint in the MATCH_ DETAILS table with reference to TEAM table so that MATCH_DETAILS table records score of teams existing in the TEAM table only.

Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the Query for the following:

1. Display the MatchID of all those matches where both teams have scored more than 70.
2. Display the MatchID of all those matches where FirstTeam has scored less than 70 but SecondTeam has scored more than 70.
3. Display the MatchID and date of matches played by Team 1 and won by it.
4. Display the MatchID of matches played by Team 2 and not won by it.
5. Change the name of the relation TEAM to T_DATA. Also, change the attributes TeamID and TeamName to T_ID and T_NAME respectively.

Differentiate between the following statement:

ALTER and UPDATE

Differentiate between the following statement:

DELETE and DROP

Create a database called STUDENT_PROJECT having the following tables. Choose appropriate data type and apply necessary constraints.

The values in Stream column can be either Science, Commerce, or Humanities.

The values in Section column can be either I or II.

1. Populate these tables with appropriate data.
2. Write SQL queries for the following.
3. Find the names of students in Science Stream.
4. What will be the primary keys of the three tables?
5. What are the foreign keys of the three relations?
6. Finds names of all the students studying in class ‘Commerce stream’ and are guided by same teacher, even if they are assigned different projects.

An organization ABC maintains a database EMPDEPENDENT to record the following details about its employees and their dependents.

EMPLOYEE(AadhaarNo, Name, Address, Department, EmpID) DEPENDENT(EmpID, DependentName, Relationship)

Use the EMP-DEPENDENT database to answer the following SQL queries

1. Find the names of employees with their dependent names.
2. Find employee details working in a department, say, ‘PRODUCTION’.
3. Find employee names having no dependent.
4. Find names of employees working in a department, say, ‘SALES’ and having exactly two dependents.

A shop called Wonderful Garments that sells school uniforms maintain a database SCHOOL UNIFORM as shown below. It consisted of two relations - UNIFORM and PRICE. They made UniformCode as the primary key for UNIFORM relation. Further, they used UniformCode and Size as composite keys for PRICE relation. By analysing the database schema and database state, specify SQL queries to rectify the following anomalies.

1. The PRICE relation has an attribute named Price. In order to avoid confusion, write SQL query to change the name of the relation PRICE to COST.
   
2. M/S Wonderful Garments also keeps handkerchiefs of red color, medium size of `100 each. Insert this record in COST table
   
3. When you used the above query to insert data, you were able to enter the values for handkerchief without entering its details in the UNIFORM relation. Make a provision so that the data can be entered in COST table only if it is already there in UNIFROM table.
4. Further, you should be able to assign a new UCode to an item only if it has a valid UName. Write a query to add appropriate constraint to the SCHOOL_UNIFORM database.
5. ALTER table to add the constraint that price of an item is always greater than zero.