Question 1

Match the following clauses with their respective functions.

ALTER - Insert the values in a table
UPDATE - Restrictions on columns
DELETE - Table definition
INSERT INTO - Change the name of a column
CONSTRAINTS - Update existing information in a table
DESC - Delete an existing row from a table
CREATE - Create a database

Accepted Answer

Correct matching:
ALTER - Change the name of a column
UPDATE - Update existing information in a table
DELETE - Delete an existing row from a table
INSERT INTO - Insert the values in a table
CONSTRAINTS - Restrictions on columns
DESC - Table definition
CREATE - Create a database

Explanation:
- ALTER is used to modify table structure such as changing column names.
- UPDATE modifies existing data in a table.
- DELETE removes rows from a table.
- INSERT INTO adds new rows.
- CONSTRAINTS define rules on columns.
- DESC shows table structure (description).
- CREATE is used to create database or tables. — Step-by-step:
1. ALTER is for altering table structure, e.g., changing column name.
2. UPDATE modifies existing data.
3. DELETE removes rows.
4. INSERT INTO adds rows.
5. CONSTRAINTS define restrictions on columns.
6. DESC describes table structure.
7. CREATE creates database or tables.

Question 2

Choose appropriate answer with respect to the following code snippet.

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

a) What will be the degree of student table?
i) 30
ii) 1
iii) 3
iv) 4

b) What does ‘name’ represent in the above code snippet?
i) a table
ii) a row
iii) a column
iv) a database

c) What is true about the following SQL statement?
Select * FROM student;
i) Displays contents of table ‘student’
ii) Displays column names and contents of table ‘student’
iii) Results in error as improper case has been used
iv) Displays only the column names of table ‘student’

d) What will be the output of following query?
INSERT INTO student
VALUES (“Suhana”,109, ^F*),
VALUES (“Rivaan”,102, ^M*),
VALUES (“Athary”,103, ^M*),
VALUES (“Rishika”,105, ^F*),
VALUES (“Garvit”,104, ^M*),
VALUES (“Shaurya”,109, ^M*);
i) Error
ii) No Error
iii) Depends on compiler
iv) Successful completion of the query

e) In the following query how many rows will be deleted?
DELETE student
WHERE student_id=109;
i) 1 row
ii) All the rows where student ID is equal to 109
iii) No row will be deleted
iv) 2 rows

Accepted Answer

Degree of student table is the number of columns. Columns are name, student_id, gender = 3 columns. So answer is iii) 3.

b) 'name' is a column in the table student. So answer is iii) a column.

c) Select * FROM student; displays all columns and all rows of the student table. So answer is ii) Displays column names and contents of table ‘student’.

d) The INSERT INTO query syntax is incorrect because VALUES keyword should be used once with multiple rows separated by commas, not repeated. Also, the gender values are shown as ^F* and ^M* which are invalid. So answer is i) Error.

e) DELETE student WHERE student_id=109; is syntactically incorrect because DELETE requires FROM keyword: DELETE FROM student WHERE student_id=109; Assuming corrected syntax, it will delete all rows where student_id=109. Since student_id is primary key, only one row can have this value. So answer is i) 1 row. — Step-by-step:
(a) Degree = number of columns = 3.
(b) 'name' is a column name.
(c) SELECT * displays all columns and rows.
(d) INSERT syntax is wrong due to repeated VALUES and invalid gender values.
(e) DELETE without FROM is wrong; corrected query deletes rows with student_id=109; only one row possible due to primary key.

Question 3

Fill in the blanks:
a) __________ declares that an index in one table is related to that in another table.
i) Primary Key
ii) Foreign Key
iii) Composite Key
iv) Secondary Key

b) The symbol Asterisk (*) in a select query retrieves
i) All data from the table
ii) Data of primary key only
iii) NULL data
iv) None of the mentioned

Accepted Answer

a) Foreign Key declares that an index in one table is related to that in another table.

b) The symbol Asterisk (*) in a select query retrieves all data from the table. — a) Foreign Key is used to link two tables by referencing primary key of another table.
b) Asterisk (*) in SELECT * retrieves all columns and all rows from the table.

Question 4

Consider the following MOVIE database and answer the SQL queries based on it.

MovieID | MovieName | Category | ReleaseDate | ProductionCost | BusinessCost
001 | Hindi_Movie | Musical | 2018-04-23 | 124500 | 130000
002 | Tamil_Movie | Action | 2016-05-17 | 112000 | 118000
003 | English_Movie | Horror | 2017-08-06 | 245000 | 360000
004 | Bengali_Movie | Adventure | 2017-01-04 | 72000 | 100000
005 | Telugu_Movie | Action | - | 100000 | -
006 | Punjabi_Movie | Comedy | - | 30500 | -

a) Retrieve movies information without mentioning their column names.
b) List business done by the movies showing only MovieID, MovieName and BusinessCost.
c) List the different categories of movies.
d) 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?
e) List all movies with ProductionCost greater than 80,000 and less than 1,25,000 showing ID, Name and ProductionCost.
f) List all movies which fall in the category of Comedy or Action.
g) List the movies which have not been released yet.

Accepted Answer

a) SQL Query: SELECT * FROM MOVIE;

b) SQL Query: SELECT MovieID, MovieName, BusinessCost FROM MOVIE;

c) SQL Query: SELECT DISTINCT Category FROM MOVIE;

d) SQL Query: SELECT MovieID, MovieName, (BusinessCost - ProductionCost) AS NetProfit FROM MOVIE;

Explanation:
- The new column NetProfit is a derived column (alias), not stored in the table permanently.
- Such columns are called computed or derived columns.
- For movies not yet released (BusinessCost or ReleaseDate missing), profit calculation may result in NULL or zero depending on data.
- Query result will show NULL or no profit for unreleased movies.

e) SQL Query: SELECT MovieID, MovieName, ProductionCost FROM MOVIE WHERE ProductionCost > 80000 AND ProductionCost < 125000;

f) SQL Query: SELECT * FROM MOVIE WHERE Category IN ('Comedy', 'Action');

g) SQL Query: SELECT * FROM MOVIE WHERE ReleaseDate IS NULL OR ReleaseDate = '-'; — Step-by-step:
a) SELECT * retrieves all columns and rows.
b) Select only specified columns.
c) DISTINCT gets unique categories.
d) Use arithmetic operation with alias for NetProfit.
e) Use WHERE with ProductionCost range.
f) Use WHERE with IN clause for categories.
g) Use WHERE with NULL or placeholder for unreleased movies.

Question 5

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:

a) Create a database "Sports".
b) Create a table "TEAM" with following considerations:
  i) It should have a column TeamID for storing an integer value between 1 to 9, which refers to 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.
c) Using table level constraint, make TeamID as primary key.
d) Show the structure of the table TEAM using SQL command.
e) As per the preferences of the students four teams were formed as given below. Insert these four rows in TEAM table:
  Row 1: (1, Team Titan)
  Row 2: (2, Team Rockers)
  Row 3: (3, Team Magnet)
  Row 4: (4, Team Hurricane)
f) Show the contents of the table TEAM.
g) Now create another table below. MATCH_DETAILS and insert data as shown in table. Choose appropriate domains and constraints for each attribute.

Table: MATCH_DETAILS

MatchID | MatchDate | FirstTeamID | SecondTeamID | FirstTeamScore | SecondTeamScore
M1 | 2018-07-17 | 1 | 2 | 90 | 86
M2 | 2018-07-18 | 3 | 4 | 45 | 48
M3 | 2018-07-19 | 1 | 3 | 78 | 56
M4 | 2018-07-19 | 2 | 4 | 56 | 67
M5 | 2018-07-20 | 1 | 4 | 32 | 87
M6 | 2018-07-21 | 2 | 3 | 67 | 51

h) 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.

Accepted Answer

a) CREATE DATABASE Sports;

b) CREATE TABLE TEAM (
  TeamID INT CHECK (TeamID BETWEEN 1 AND 9),
  TeamName VARCHAR(50) CHECK (LENGTH(TeamName) >= 10)
);

c) ALTER TABLE TEAM ADD CONSTRAINT pk_TeamID PRIMARY KEY (TeamID);

Alternatively, define primary key in CREATE TABLE:
CREATE TABLE TEAM (
  TeamID INT PRIMARY KEY CHECK (TeamID BETWEEN 1 AND 9),
  TeamName VARCHAR(50) CHECK (LENGTH(TeamName) >= 10)
);

d) To show structure:
DESC TEAM;

or
SHOW COLUMNS FROM TEAM;

e) Insert rows:
INSERT INTO TEAM VALUES (1, 'Team Titan');
INSERT INTO TEAM VALUES (2, 'Team Rockers');
INSERT INTO TEAM VALUES (3, 'Team Magnet');
INSERT INTO TEAM VALUES (4, 'Team Hurricane');

f) Show contents:
SELECT * FROM TEAM;

g) Create MATCH_DETAILS table:
CREATE TABLE MATCH_DETAILS (
  MatchID VARCHAR(5) PRIMARY KEY,
  MatchDate DATE,
  FirstTeamID INT,
  SecondTeamID INT,
  FirstTeamScore INT,
  SecondTeamScore INT,
  CONSTRAINT fk_FirstTeam FOREIGN KEY (FirstTeamID) REFERENCES TEAM(TeamID),
  CONSTRAINT fk_SecondTeam FOREIGN KEY (SecondTeamID) REFERENCES TEAM(TeamID)
);

Insert data:
INSERT INTO MATCH_DETAILS VALUES ('M1', '2018-07-17', 1, 2, 90, 86);
INSERT INTO MATCH_DETAILS VALUES ('M2', '2018-07-18', 3, 4, 45, 48);
INSERT INTO MATCH_DETAILS VALUES ('M3', '2018-07-19', 1, 3, 78, 56);
INSERT INTO MATCH_DETAILS VALUES ('M4', '2018-07-19', 2, 4, 56, 67);
INSERT INTO MATCH_DETAILS VALUES ('M5', '2018-07-20', 1, 4, 32, 87);
INSERT INTO MATCH_DETAILS VALUES ('M6', '2018-07-21', 2, 3, 67, 51);

h) Foreign key constraints are added in MATCH_DETAILS table creation as shown above to ensure FirstTeamID and SecondTeamID values exist in TEAM table.

Explanation:
- Database creation is straightforward.
- Table TEAM has constraints on TeamID and TeamName length.
- Primary key constraint ensures uniqueness.
- Foreign key constraints maintain referential integrity between MATCH_DETAILS and TEAM.
- Data insertion follows the table structure. — Step-by-step:
1. Create database.
2. Create TEAM table with constraints.
3. Add primary key.
4. Show structure using DESC.
5. Insert team data.
6. Show contents.
7. Create MATCH_DETAILS with appropriate data types and foreign keys.
8. Insert match data.
9. Foreign keys ensure team IDs in MATCH_DETAILS exist in TEAM.

Question 6

Which of the following best describes Structured Query Language (SQL)?

Accepted Answer

A standard language for managing and manipulating relational databases — SQL is a standard programming language specifically designed for managing and manipulating relational databases. It allows users to create, retrieve, update, and delete data stored in tables within a database.

Question 7

Which category of SQL commands is used to define and modify the structure of database objects such as tables and indexes?

Accepted Answer

Data Definition Language (DDL) — DDL commands like CREATE, ALTER, and DROP are used to define and modify the structure of database objects such as tables, indexes, and schemas.

Question 8

Identify the SQL command used to permanently delete an existing table from a database.

Accepted Answer

DROP TABLE — The DROP TABLE command deletes an existing table permanently from the database, removing both its structure and data.

Question 9

Which of the following is NOT a valid data type in SQL for defining a column?

Accepted Answer

ALPHANUMERIC — ALPHANUMERIC is not a standard SQL data type. VARCHAR is used for variable-length character strings, INTEGER for whole numbers, and DATE for date values.

Question 10

Which SQL constraint ensures that a column cannot have NULL values and uniquely identifies each record in a table?

Accepted Answer

PRIMARY KEY — PRIMARY KEY constraint uniquely identifies each record in a table and does not allow NULL values, ensuring data integrity.

Question 11

What is the main purpose of the Data Manipulation Language (DML) in SQL?

Accepted Answer

To manipulate data stored in database tables — DML commands such as SELECT, INSERT, UPDATE, and DELETE allow users to manipulate data within tables without changing the structure.

Question 12

Which SQL command is used to add new records to a table?

Accepted Answer

INSERT — The INSERT command adds new rows (records) into a table.

Question 13

Which clause in an SQL SELECT statement is used to filter records based on specified conditions?

Accepted Answer

WHERE — The WHERE clause filters records in SELECT, UPDATE, and DELETE statements to apply conditions on the data.

Question 14

Which aggregate function in SQL returns the average value of a numeric column?

Accepted Answer

AVG — AVG function calculates the average (mean) value of a numeric column.

Question 15

Which of the following SQL commands is used to provide specific privileges to a user on database objects?

Accepted Answer

GRANT — The GRANT command is used to provide users with specific privileges such as SELECT, INSERT, UPDATE, and DELETE on database objects.

Introduction to Chapter Structured Query 8 Language (SQL)

Introduction to Chapter Structured Query 8 Language (SQL) — Study Notes

8.1 INTRODUCTION

8.2 STRUCTURED QUERY LANGUAGE (SQL)

8.3 DATA TYPES AND CONSTRAINTS IN MySQL

Practice Questions — Introduction to Chapter Structured Query 8 Language (SQL)

All 8 Chapters in Informatics Practices