Structured Query Language (SQL)

MySQL is a freely available open-source RDBMS that implements SQL.

MySQL provides a dummy table called dual.

The distinct keyword eliminates duplicate records from the results of a SELECT statement.

The Describe/DESC statement is used to view the structure of a table.

The Where clause is used to select specific rows.

Select is used to fetch data from one or more database tables.

Select * statement displays all Columns of a table.

The rows of the table (relation) are referred to as Tuples.

Insert into command is used to add a new record in a table.

The non-key attribute which helps to make relationship between two tables is known as Foreign key.

ALTER table command is used to change the data type of an existing column.

The statement SELECT * FROM Student ORDER BY Marks DESC; will display marks in Descending order.

Duplication of data is known as Data Redundancy.

Answer

True

Reason — Data redundancy refers to the duplication of data in a database.

An attribute is a set of values of a dissimilar type of data.

Answer

False

Reason — An attribute (column) is a set of values of similar type of data.

MySQL supports different platforms like UNIX and Windows.

Answer

True

Reason — MySQL is a cross-platform database management system that runs on various operating systems, including UNIX and Windows.

UPDATE TABLE command is used to create table in a database.

Answer

False

Reason — CREATE TABLE command is used to create table in a database.

Null (Unavailable and unknown) values are entered by the following command:

INSERT INTO TABLE NAME VALUES ("NULL") ;

Answer

False

Reason — The correct syntax for inserting a NULL value is : INSERT INTO TABLE_NAME VALUES (NULL, NULL, ...); or INSERT INTO TABLE_NAME (COLUMN_NAME) VALUES (NULL);.

ALTER TABLE command is used to modify the structure of the table.

Answer

True

Reason — The ALTER TABLE command in SQL is used to modify the definition (structure) of a table by modifying the definition of its columns.

Each SQL table must have at least one column and one row.

Answer

False

Reason — Each SQL table must have at least one column, there is no requirement for it to have at least one row.

Foreign key column derives its value from the primary key of the parent table.

Answer

True

Reason — Foreign key column refers to value of an attribute defined as the primary key in another table.

DISTINCT clause is used to remove redundant rows from the result of the SELECT statement.

Answer

True

Reason — The DISTINCT keyword in SQL is used to eliminate duplicate rows from the results of the SELECT statement.

It is not necessary to create primary key in a table.

Answer

False

Reason — A primary key is necessary in a table as it uniquely identifies each record.

All primary keys are candidate keys but all candidate keys are not primary keys.

Answer

True

Reason — A primary key is a candidate key chosen to uniquely identify rows in a table. All primary keys are candidate keys, but not all candidate keys become primary keys, as only one is selected for that role.

The ............... allows you to perform tasks related to data definition.

1. DDL
2. DML
3. TCL
4. None of these

Answer

DDL

Reason — The DDL (Data Definition Language) commands allow us to perform tasks related to data definition, i.e., related to the structure of the database objects.

The ............... allows you to perform tasks related to data manipulation.

1. DDL
2. DML
3. TCL
4. None of these

Answer

DML

Reason — The DML (Data Manipulation Language) commands are used to manipulate data, i.e., records or rows in a table or relation.

A ............... is a text that is not executed.

1. Statement
2. Query
3. Comment
4. Clause

Answer

Comment

Reason — A comment is a text which is ignored by the SQL compiler and is not executed at all. It is given for documentation purpose only.

............... are words that have a special meaning in SQL.

1. Keywords
2. Literal
3. Variable
4. Table

Answer

Keywords

Reason — A keyword refers to an individual SQL element that has a special meaning in SQL.

............... command helps to open the database for use.

1. Use
2. Open
3. Distinct
4. Select

Answer

Use

Reason — The USE command is used to open the database for use. The syntax for opening database is : USE <database_name>;.

............... command helps to fetch data from a relation.

1. Use
2. Show
3. Fetch
4. Select

Answer

Select

Reason — The SELECT command is used to retrieve a subset of rows or columns from one or more tables present in a database.

The ............... keyword eliminates duplicate rows from the results of a SELECT statement.

1. Or
2. Distinct
3. Any
4. All

Answer

Distinct

Reason — The DISTINCT keyword in SQL is used to eliminate duplicate rows from the results of a SELECT statement.

............... command helps to see the structure of a table/relation.

1. Show
2. Select
3. Describe
4. Order by

Answer

Describe

Reason — To view a table structure, DESCRIBE or DESC command is used. It shows the structure of the table along with the name of the columns, data type of the columns and constraints applied on the columns.

Which of the following types of table constraints will prevent the entry of duplicate rows?

1. Foreign Key
2. Distinct
3. Primary Key
4. NULL

Answer

Primary Key

Reason — A primary key is a set of one or more attributes or fields that uniquely identifies a tuple or row in a table. Therefore, it ensures that each row in the table is unique and prevents the entry of duplicate rows.

Which command shows the list of database in MySQL?

1. desc databases;
2. show tables;
3. show databases;
4. describe databases;

Answer

show databases;

Reason — The SHOW DATABASES; command lists all the databases managed by the MySQL server.

Which of the following are DDL commands?

(A) Delete

(B) Create

(C) Update

(D) Alter

(E) Drop

1. (B), (D) and (E)
2. (A), (B) and (D)
3. (B), (C) and (D)
4. (A), (B) and (C)

Answer

(B), (D) and (E)

Reason — DDL (Data Definition Language) commands are used to create and define tables and other database objects in SQL (Structured Query Language). DDL commands such as CREATE, ALTER, and DROP, are used to create, define, change and delete objects like tables, indexes, views, and constraints.

Identify the correct statement(s):

Statement 1 (S1): Char data-type in MySQL stores fixed length strings.

Statement 2 (S2): Char data-type stores string smaller than the maximum field size.

1. (S1) : Correct, (S2) : Correct
2. (S1) : Incorrect, (S2) : Correct
3. (S1) : Correct, (S2) : Incorrect
4. (S1) : Incorrect, (S2) : Incorrect

Answer

(S1) : Correct, (S2) : Incorrect

Reason — The CHAR data type provides fixed-length memory storage. It specifies a fixed-length character string. If the input string is shorter, MySQL pads it with spaces to fill the fixed length. If the input string is longer, it is truncated to fit the fixed length.

Which of the following keywords is used to display non-repeated values in MySQL?

1. Unique
2. All
3. Order by
4. Distinct

Answer

Distinct

Reason — The DISTINCT keyword is used to display the unique values of the column in MySQL.

The SQL statements always end with ............... .

1. ,
2. :
3. ;
4. "

Answer

;

Reason — The SQL statements always end with semicolon (;).

Shivam wants to see the table structure in MySQL. Select an appropriate command to help him.

1. Use
2. Show
3. Desc
4. Display

Answer

Desc

Reason — The DESCRIBE or DESC command is used to view a table structure in MySQL.

Rajat wants to delete a primary key constraint from the table. Select an appropriate command to do so.

1. Create
2. Alter
3. Drop
4. Delete

Answer

Alter

Reason — The ALTER TABLE command is used to delete a primary key constraint from the table.

Rajveer wants to rename column in display result for his query. Select the appropriate query for the same:

1. Select Ename, Salary*12 Annual Salary From Emp;
2. Select Ename, Salary*12 Rename "Annual Salary" From Emp;
3. Select Ename, Salary* 12 Change "Annual Salary" From Emp;
4. Select Ename, Salary*12 as "Annual Salary" From Emp;

Answer

Select Ename, Salary*12 as "Annual Salary" From Emp;

Reason — The AS keyword is used to give an alias to a column or expression in the SELECT statement. In this case, AS "Annual Salary" is used to rename the column Salary*12 to "Annual Salary" in the display result.

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

1. All data from the table
2. Data of primary key only
3. NULL data
4. None of these

Answer

All data from the table

Reason — The asterisk symbol (*) is a wildcard character in SQL that retrieves all columns or fields from a table. When used in a SELECT statement, it returns all columns and rows from the specified table.

Consider the attributes ( RollINumber, SName, SDateofBirth, GUID ) of the table Student. According to you, which of the following options is the correct representation of the table after executing the following query?

Insert Into Student (RollNumber, SName, SDateofBirth) 
Values (2, 'Sudha', '2002-02-28') ;  

(i)

(ii)

(iii)

(iv)

Answer

Reason — In the above code, a new row is inserted into the 'Student' table, values are provided for RollNumber, SName, and SDateofBirth, but not for GUID. Since GUID is not specified, it will take on its default value, which may be NULL if no default value is defined for the column.

Consider a table Student having two fields—FName varchar(20) and LName char(20). If in a record, value stored in Fname is 'Anuj' and LName is 'Batra', then FName and LName will consume ...............and ............... Character space, respectively.

1. 4, 5
2. 4, 20
3. 20, 4
4. 20, 20

Answer

4, 20

Reason — FName is a varchar(20) field, which means it can store a variable-length string up to a maximum of 20 characters. Since the value stored in FName is 'Anuj', it will consume 4 character spaces (A-n-u-j). LName is a char(20) field, which means it is a fixed-length string that always occupies 20 character spaces, regardless of the actual length of the string. Since the value stored in LName is 'Batra', it will still consume 20 character spaces, with the remaining 15 characters being padded with spaces.

Assertion (A): A database can have only one table in MySql.

Reasoning (R): If a piece of data is stored in two places in the databases, then storage space is wasted.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

A is false but R is true.

Explanation
A database can have multiple tables. Data redundancy, which occurs when the same data is repeated in multiple places within a database, leads to wastage of storage space. A DBMS eliminates data redundancy by integrating files, ensuring that multiple copies of the same data are not stored.

Assertion (A): A database constraint can be added or removed any time in/from the database tables.

Reasoning (R): Alter table command is used to change the structure of the table.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

Both A and R are true but R is not the correct explanation of A.

Explanation
A database constraint can be added or removed from database tables using the ALTER TABLE command, even after the table has already been created. This command is used to modify the structure of a table by altering the definition of its columns.

Assertion (A): SQL has efficient mechanisms to retrieve data stored in multiple tables in a MySQL database.

Reasoning (R): The SQL statement CREATE is used to retrieve data from the tables in a database and is also called query statement.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

A is true but R is false.

Explanation
SQL provides efficient mechanisms, such as JOIN operations, to retrieve data from multiple tables in a MySQL database. The SQL statement CREATE is used to create new database objects such as tables, indexes, or views. The SELECT statement is used to retrieve data from tables in a database and is known as a query statement.

Assertion (A): The SQL keyword Like is used with wildcards only.

Reasoning (R): '_' underscore and "%" per cent are the two wildcard characters used with LIKE clause.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

Both A and R are true and R is the correct explanation of A.

Explanation
The SQL LIKE keyword allows the use of wildcard characters to perform pattern matching. SQL provides two wildcard characters to use with the LIKE operator: the percent sign (%) which matches any string, and the underscore ("_") which matches any single character.

Assertion (A): DISTINCT clause must be used in an SQL statement to eliminate duplicate rows.

Reasoning (R): DISTINCT works with numeric data type only.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

A is true but R is false.

Explanation
The DISTINCT clause is used to remove duplicate rows from the results of a SELECT statement. It retrieves only unique values for a column in the table. The DISTINCT keyword in SQL can be used with any data type.

Assertion (A): ORDER BY clause is used to sort the records of a table.

Reasoning (R): SQL provides two keywords for sorting in ascending and descending orders, ASC and DESC.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

Both A and R are true and R is the correct explanation of A.

Explanation
The SQL ORDER BY clause is used to sort the data is ascending or descending order based on one or more columns. This clause sorts the records in ascending order (ASC) by default. To sort the records in descending order, DESC keyword is used.

Assertion (A): FLOAT and DOUBLE are data types.

Reasoning (R): Both can hold any number up to 23 digits.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

A is true but R is false.

Explanation
FLOAT and DOUBLE are data types in SQL, used to store decimal numbers. FLOAT can store values with a precision of around 6-7 digits, while DOUBLE can store values with a precision of around 15-16 digits.

What is an Alternate Key?

Answer

A candidate key that is not the primary key is called an alternate key.

Define Relation.

Answer

A relation is a table i.e., data arranged in rows and columns.

Define tuple.

Answer

The rows of tables (relations) are called tuples.

Define attribute.

Answer

The columns of tables (relations) are called attributes.

Define domain.

Answer

A domain is a pool of values from which the actual values appearing in a given column are drawn.

What do you understand by the terms candidate key and alternate key in relational database?

Answer

All attribute combinations inside a relation that can serve as primary key are candidate keys as they are candidates for the primary key position.

A candidate key that is not the primary key is called an alternate key. In other words, any attribute that is a candidate for the primary key, i.e., which is capable of becoming a primary key but is not a primary key, is an alternate key.

What is SQL? What are the different categories of commands available in SQL?

Answer

SQL (Structured Query Language) is a standard language for accessing and manipulating databases.

The different categories of SQL commands are as follows:

1. Data Definition Language (DDL) Commands
2. Data Manipulation Language (DML) Commands
3. Data Query Language (DQL) command
4. Data Control Language (DCL) commands
5. Transaction Control Language (TCL) Commands

What is a database system? What is its need?

Answer

A Database Management System is a general purpose software system that facilitates the process of defining, constructing and manipulating databases for various applications. The database system is used to eliminate the problems of data redundancy and data inconsistency.

Differentiate between DDL and DML commands.

Answer

What is a data type? Name some data types available in MySQL.

Answer

Data types are means to identify the type of data and associated operations for handling it. The data types available in MySQL are int, float, date, time, char, varchar etc.

Differentiate between char and varchar data types.

Answer

Which operator concatenates two strings in a query result?

Answer

The + operator is used to concatenate two strings in a query result.

How would you calculate 13*15 in SQL?

Answer

To calculate 13 * 15 in SQL, we can use SELECT statement to retrieve rows computed without reference to any table. For example,

mysql> SELECT 13 * 15;

Which keyword eliminates the redundant data from a query?

Answer

DISTINCT keyword eliminates the redundant data from a query result.

Write SQL queries to perform the following based on the table Product having fields as (prod_id, prod_name, quantity, unit_rate, price, city)

(a) Display those records from table Product where prod_id is more than 100.

(b) List records from table Product where prod_name is 'Almirah'.

(c) List all those records whose price is between 200 and 500.

(d) Display the product names whose quantity is not given.

(e) Show the detailed records in the table Product.

Answer

(a)

SELECT * FROM PRODUCT
WHERE prod_id > 100;

(b)

SELECT * FROM PRODUCT
WHERE prod_name = 'Almirah';

(c)

SELECT * FROM PRODUCT
WHERE price BETWEEN 200 AND 500; 

(d)

SELECT prod_name
FROM PRODUCT
WHERE quantity IS NULL;

(e)

SELECT * FROM PRODUCT;

Define Database.

Answer

A database is defined as a collection of interrelated data stored together to serve multiple applications.

Define Data Inconsistency.

Answer

Mismatched multiple copies of same data is known as data inconsistency.

Define Primary Key.

Answer

A primary key is a set of one or more attributes that can uniquely identify tuples within the relation.

Define Candidate Key.

Answer

All attribute combinations inside a relation that can serve as primary key are candidate keys as they are candidates for the primary key position.

Match the following clauses with their respective functions.

Answer

Differentiate between the following commands:

(a) ALTER and UPDATE

(b) DELETE and DROP

Answer

(a) Differences between ALTER and UPDATE statements:

(ii) Differences between DELETE and DROP statements:

Consider the following tables STORE and SUPPLIERS. Write SQL commands for the statements (i) to (iii) and give the output for SQL query (iv).

Table: STORE

Table: SUPPLIERS

(i) To display details of all the items in the Store table.

(ii) To display ItemNo and item name of those items from store table whose rate is more than 15.

(iii) To display the details of those items whose supplier code is 22 or Quantity in store is more than 110 from the table Store.

(iv) SELECT Rate*Qty FROM STORE WHERE Itemno = 2004;

Answer

(i)

SELECT * FROM STORE;

+--------+-------------------+-------+-----+------+------------+
| ItemNo | Item              | Scode | Qty | Rate | LastBuy    |
+--------+-------------------+-------+-----+------+------------+
|   2001 | Eraser Small      |    22 | 220 |    6 | 2009-01-19 |
|   2002 | Gel Pen Premium   |    21 | 150 |   12 | 2010-02-24 |
|   2003 | Ball Pen 0.25     |    22 |  50 |   25 | 2010-02-01 |
|   2004 | Eraser Big        |    22 | 110 |    8 | 2009-12-02 |
|   2005 | Sharpener Classic |    23 |  60 |    8 | 2009-06-30 |
|   2006 | Gel Pen Classic   |    21 | 250 |   20 | 2009-03-11 |
|   2009 | Ball Pen 0.5      |    21 | 180 |   18 | 2009-11-03 |
+--------+-------------------+-------+-----+------+------------+

(ii)

SELECT ItemNo, Item FROM STORE WHERE Rate > 15;

+--------+-----------------+
| ItemNo | Item            |
+--------+-----------------+
|   2003 | Ball Pen 0.25   |
|   2006 | Gel Pen Classic |
|   2009 | Ball Pen 0.5    |
+--------+-----------------+

(iii)

SELECT * FROM STORE WHERE Scode = 22 OR Qty > 110;

+--------+-----------------+-------+-----+------+------------+
| ItemNo | Item            | Scode | Qty | Rate | LastBuy    |
+--------+-----------------+-------+-----+------+------------+
|   2001 | Eraser Small    |    22 | 220 |    6 | 2009-01-19 |
|   2002 | Gel Pen Premium |    21 | 150 |   12 | 2010-02-24 |
|   2003 | Ball Pen 0.25   |    22 |  50 |   25 | 2010-02-01 |
|   2004 | Eraser Big      |    22 | 110 |    8 | 2009-12-02 |
|   2006 | Gel Pen Classic |    21 | 250 |   20 | 2009-03-11 |
|   2009 | Ball Pen 0.5    |    21 | 180 |   18 | 2009-11-03 |
+--------+-----------------+-------+-----+------+------------+

(iv) SELECT Rate*Qty FROM STORE WHERE Itemno = 2004;

+------------+
| Rate * Qty |
+------------+
|        880 |
+------------+

An organization ABC maintains a database EMP-DEPENDENT 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:

(i) Find the names of the employees with their dependents' names.

(ii) Find employee details working in a department, say, 'PRODUCTION'.

(iii) Find employee names having no dependents.

(iv) Find the names of employees working in a department, say, 'SALES' and having exactly two dependents.

Answer

(i)

SELECT e.Name, d.DependentName
FROM EMPLOYEE e, DEPENDENT d 
WHERE e.EmpID = d.EmpID;

(ii)

SELECT *
FROM EMPLOYEE
WHERE Department = 'PRODUCTION';

(iii)

SELECT e.Name
FROM EMPLOYEE e, DEPENDENT d 
WHERE e.EmpID = d.EmpID AND d.DependentName IS NULL;

(iv)

SELECT Name
FROM EMPLOYEE
WHERE Department = 'SALES' AND EmpID IN (
    SELECT EmpID
    FROM DEPENDENT
    GROUP BY EmpID
    HAVING COUNT(*) = 2
);

Write SQL commands for (i) to (v) on the basis of relation given below:

Table: BOOKS

(i) To show the books of FIRST PUBL. written by J. Mukhi.

(ii) To display cost of all the books published for FIRST PUBL.

(iii) Depreciate the price of all books of EPB publishers by 5%.

(iv) To display the Book_Name and price of the books more than 3 copies of which have been issued.

(v) To show the details of the book with quantity more than 30.

Answer

(i)

SELECT * 
FROM BOOKS 
WHERE Publishers = 'FIRST PUBL.' AND author_name = 'J. Mukhi';

+---------+-----------+-------------+-------------+--------+---------+-----+
| book_id | Book_name | author_name | Publishers  | Price  | Type    | qty |
+---------+-----------+-------------+-------------+--------+---------+-----+
| p0001   | Genuine   | J. Mukhi    | FIRST PUBL. | 755.00 | Fiction |  24 |
+---------+-----------+-------------+-------------+--------+---------+-----+

(ii)

SELECT SUM(Price) AS TotalCost 
FROM BOOKS 
WHERE Publishers = 'FIRST PUBL.';

+-----------+
| TotalCost |
+-----------+
|   1105.00 |
+-----------+

(iii)

UPDATE BOOKS 
SET Price = Price - (Price * 0.05)
WHERE Publishers = 'EPB';

(iv)

SELECT Book_name, Price 
FROM BOOKS 
WHERE qty > 3;

+-------------------------+--------+
| Book_name               | Price  |
+-------------------------+--------+
| Let us C                | 427.50 |
| Programming with Python | 350.00 |
| Mastering C++           | 156.75 |
| VC++ advance            | 250.00 |
| Genuine                 | 755.00 |
+-------------------------+--------+

(v)

SELECT * 
FROM BOOKS 
WHERE qty > 30;

+---------+---------------+----------------+------------+--------+------+-----+
| book_id | Book_name     | author_name    | Publishers | Price  | Type | qty |
+---------+---------------+----------------+------------+--------+------+-----+
| m0001   | Mastering C++ | K.R. Venugopal | EPB        | 156.75 | Comp |  60 |
| n0002   | VC++ advance  | P. Purohit     | TDH        | 250.00 | Comp |  45 |
+---------+---------------+----------------+------------+--------+------+-----+

Write SQL commands for (a) to (e) on the basis of PRODUCTS relation given below:

Table: PRODUCTS

(a) To show details of all PCs with stock more than 110.

(b) To list the company which gives warranty of more than 2 years.

(c) To find stock value of the BPL company where stock value is the sum of the products of price and stock.

(d) To show products from PRODUCTS table.

(e) To show the product name of the products which are within warranty as on date.

Answer

(a)

SELECT *
FROM PRODUCTS
WHERE PNAME = 'PC' AND STOCK > 110;

+-------+-------+---------+-------+-------+-------------+----------+
| PCODE | PNAME | COMPANY | PRICE | STOCK | MANUFACTURE | WARRANTY |
+-------+-------+---------+-------+-------+-------------+----------+
| P004  | PC    | COMPAQ  | 38000 |   120 | 2019-06-20  |        2 |
+-------+-------+---------+-------+-------+-------------+----------+

(b)

SELECT DISTINCT COMPANY
FROM PRODUCTS
WHERE WARRANTY > 2;

+---------+
| COMPANY |
+---------+
| BPL     |
| SONY    |
+---------+

(c)

SELECT COMPANY, SUM(PRICE * STOCK) AS StockValue 
FROM PRODUCTS 
WHERE COMPANY = 'BPL';

+---------+------------+
| COMPANY | StockValue |
+---------+------------+
| BPL     |    2000000 |
+---------+------------+

(d)

SELECT * FROM PRODUCTS;

+-------+----------+---------+-------+-------+-------------+----------+
| PCODE | PNAME    | COMPANY | PRICE | STOCK | MANUFACTURE | WARRANTY |
+-------+----------+---------+-------+-------+-------------+----------+
| P001  | TV       | BPL     | 10000 |   200 | 2018-01-12  |        3 |
| P002  | TV       | SONY    | 12000 |   150 | 2017-03-23  |        4 |
| P003  | PC       | LENOVO  | 39000 |   100 | 2018-04-09  |        2 |
| P004  | PC       | COMPAQ  | 38000 |   120 | 2019-06-20  |        2 |
| P005  | HANDYCAM | SONY    | 18000 |   250 | 2017-03-23  |        3 |
+-------+----------+---------+-------+-------+-------------+----------+

(e)

SELECT PNAME 
FROM PRODUCTS 
WHERE DATE_ADD(MANUFACTURE, INTERVAL WARRANTY YEAR) >= CURDATE();

There is no output produced because the warranty of all products has expired.

What are DDL and DML?

Answer

The Data Definition Language (DDL) part of SQL permits the creation or deletion of database tables. It also defines indices (keys), specifies links between tables, and imposes constraints on tables. DDL contains necessary statements for creating, manipulating, altering, and deleting tables. Data Manipulation Language (DML) is a part of SQL that helps users manipulate data. It contains necessary statements for inserting, updating, and deleting data.

Differentiate between primary key and candidate key in a relation.

Answer

What do you understand by the terms Cardinality and Degree of a relation in relational database?

Answer

The number of tuples/rows in a relation is called the Cardinality of the relation.

The number of attributes/columns in a relation is called the Degree of the relation.

Differentiate between DDL and DML. Mention the two commands for each category.

Answer

Consider the given table and answer the questions.

Table: SCHOOLBUS

(a) To show all information of schoolbus where capacity is more than 70.

(b) To show area_covered for buses covering more than 20 km but charges less than 4000.

(c) To show transporter-wise details along with their charges.

(d) To show Rtno, Area_Covered and Average cost per student for all routes where average cost per student is—Charges/Noofstudents.

(e) Add a new record with the following data:
(11, "Motibagh", 35, 32, 10, "Kisan Tours", 3500)

Answer

(a)

SELECT * 
FROM SCHOOLBUS 
WHERE CAPACITY > 70;

+------+--------------+----------+--------------+----------+----------------+---------+
| Rtno | Area_Covered | Capacity | Noofstudents | Distance | Transporter    | Charges |
+------+--------------+----------+--------------+----------+----------------+---------+
|    1 | Vasant Kunj  |      100 |          120 |       10 | Shivam Travels | 3500.00 |
|    2 | Hauz Khas    |       80 |           80 |       10 | Anand Travels  | 3000.00 |
|    4 | Rohini       |      100 |           90 |       35 | Anand Travels  | 5000.00 |
|    7 | Vasundhara   |      100 |          110 |       20 | Yadav Travels  | 3500.00 |
|    9 | Saket        |      120 |          120 |       10 | Speed Travels  | 3500.00 |
|   10 | Janakpuri    |      100 |          100 |       20 | Kisan Tours    | 3500.00 |
+------+--------------+----------+--------------+----------+----------------+---------+

(b)

SELECT AREA_COVERED 
FROM SCHOOLBUS
WHERE DISTANCE > 20 AND CHARGES < 4000;

(c)

SELECT Transporter, SUM(Charges) AS Total_Charges
FROM SCHOOLBUS
GROUP BY Transporter;

+----------------+---------------+
| Transporter    | Total_Charges |
+----------------+---------------+
| Shivam Travels |          3500 |
| Anand Travels  |         12500 |
| Bhalla Travels |          3800 |
| Yadav Travels  |          7500 |
| Speed Travels  |          6700 |
| Kisan Tours    |          3500 |
+----------------+---------------+

(d)

SELECT RTNO, AREA_COVERED, (CHARGES/NOOFSTUDENTS) AS AVERAEG_COST 
FROM SCHOOLBUS;

+------+---------------+--------------+
| RTNO | AREA_COVERED  | AVERAEG_COST |
+------+---------------+--------------+
|    1 | Vasant Kunj   |    29.166667 |
|    2 | Hauz Khas     |    37.500000 |
|    3 | Pitampura     |    81.818182 |
|    4 | Rohini        |    55.555556 |
|    5 | Yamuna Vihar  |    63.333333 |
|    6 | Krishna Nagar |    50.000000 |
|    7 | Vasundhara    |    31.818182 |
|    8 | Paschim Vihar |    80.000000 |
|    9 | Saket         |    29.166667 |
|   10 | Janakpuri     |    35.000000 |
+------+---------------+--------------+

(e)

INSERT INTO SCHOOLBUS 
VALUES(11, "MOTIBAGH", 35, 32, 10, "KISAN TOURS", 3500);

Write SQL commands for (a) to (d) and write the output for (e) on the basis of the following table:

Table: FURNITURE

(a) To list the details of furniture whose price is more than 10000.

(b) To list the Item name and Price of furniture whose discount is between 10 and 20.

(c) To delete the record of all items where discount is 30.

(d) To display the price of 'BabyCot'.

(e) Select Distinct Type from Furniture;

Answer

(a)

SELECT * FROM FURNITURE
WHERE PRICE > 10000;

+----+-------------+-------------+-------------+-------+----------+
| NO | ITEM        | TYPE        | DATEOFSTOCK | PRICE | DISCOUNT |
+----+-------------+-------------+-------------+-------+----------+
|  4 | Decent      | OfficeTable | 2002-02-01  | 25000 |       30 |
|  5 | Comfortzone | DoubleBed   | 2002-02-12  | 25000 |       30 |
+----+-------------+-------------+-------------+-------+----------+

(b)

SELECT ITEM, PRICE
FROM FURNITURE
WHERE DISCOUNT BETWEEN 10 AND 20;

+--------------+-------+
| ITEM         | PRICE |
+--------------+-------+
| Pinkfeathers |  7000 |
| Dolphin      |  9500 |
| Donald       |  6500 |
+--------------+-------+

(c)

DELETE FROM FURNITURE WHERE DISCOUNT = 30;

(d)

SELECT PRICE
FROM FURNITURE
WHERE TYPE = 'BabyCot';

+-------+
| PRICE |
+-------+
|  7000 |
|  9500 |
|  6500 |
+-------+

(e)

SELECT DISTINCT Type FROM Furniture;

+-------------+
| Type        |
+-------------+
| DoubleBed   |
| BabyCot     |
| OfficeTable |
+-------------+

Write SQL commands for (a) to (d) and write the output for (e) and (f) on the basis of given table GRADUATE:

Table: GRADUATE

(a) List the names of those students who have obtained rank 1.

(b) Display a list of all those names whose average is greater than 65.

(c) Display the names of those students who have opted computer as a subject with average of more than 60.

(d) List the names of all students.

(e) SELECT * FROM GRADUATE WHERE Subject = "Physics";

(f) SELECT RANK FROM GRADUATE;

Answer

(a)

SELECT NAME FROM GRADUATE 
WHERE `RANK` = 1;

+--------+
| NAME   |
+--------+
| KARAN  |
| RAJ    |
| DIVYA  |
| GAURAV |
| VARUN  |
| LIZA   |
| PUJA   |
+--------+

(b)

SELECT NAME
FROM GRADUATE
WHERE AVERAGE > 65;

+--------+
| NAME   |
+--------+
| KARAN  |
| RAJ    |
| GAURAV |
| LIZA   |
+--------+

(c)

SELECT NAME
FROM GRADUATE
WHERE SUBJECT = 'COMPUTER' AND AVERAGE > 60;

+------+
| NAME |
+------+
| LIZA |
+------+

(d)

SELECT name 
FROM GRADUATE;

+--------+
| name   |
+--------+
| KARAN  |
| RAJ    |
| DEEP   |
| DIVYA  |
| GAURAV |
| MANAV  |
| VARUN  |
| LIZA   |
| PUJA   |
| NISHA  |
+--------+

(e)

SELECT * FROM GRADUATE WHERE Subject = "Physics";

+-------+--------+---------+---------+---------+------+
| S.No. | name   | stipend | subject | average | RANK |
+-------+--------+---------+---------+---------+------+
|     1 | KARAN  |     400 | PHYSICS |      68 |    1 |
|     5 | GAURAV |     500 | PHYSICS |      70 |    1 |
|     9 | PUJA   |     500 | PHYSICS |      62 |    1 |
+-------+--------+---------+---------+---------+------+

(f) Since 'RANK' is a reserved keyword in SQL, we encounter an error while running this query. To avoid such errors, we can enclose the column name 'RANK' in backticks to treat it as a literal identifier.

The corrected query is :

SELECT `RANK` FROM GRADUATE;

+------+
| RANK |
+------+
|    1 |
|    1 |
|    2 |
|    1 |
|    1 |
|    2 |
|    1 |
|    1 |
|    1 |
|    2 |
+------+

What is the difference between Candidate key and Alternate key?

Answer

What is the degree and cardinality of a table having 10 rows and 5 columns?

Answer

The degree of a table is 5 and the cardinality of a table is 10.

For the given table, do as directed:

Table:STUDENT

(i) Write SQL query to create the table.

(ii) Write SQL query to increase the size of SNAME to hold 30 characters.

(iii) Write SQL query to remove the column HOBBY.

(iv) Write SQL query to insert a row in the table with any values of your choice that can be accommodated there.

Answer

(i)

CREATE TABLE STUDENT(
    ROLLNO INT(4) PRIMARY KEY,
    SNAME VARCHAR(25) NOT NULL,
    GENDER CHAR(1) NOT NULL,
    DOB DATE NOT NULL,
    FEES INT(4) NOT NULL,
    HOBBY VARCHAR(15)
);

(ii)

ALTER TABLE STUDENT MODIFY SNAME VARCHAR(30);

(iii)

ALTER TABLE STUDENT DROP HOBBY;

(iv)

INSERT INTO STUDENT(ROLLNO, SNAME, GENDER, DOB, FEES, HOBBY)
VALUES (1, 'ANANYA', 'F', '2000-01-01', 5000, 'COOKING');

Write SQL queries based on the following tables:

Table: PRODUCT

Table: CLIENT

(i) Write SQL query to display ProductName and Price for all products whose Price is in the range 50 to 150.

(ii) Write SQL Query to display details of products whose manufacturer is either XYZ or ABC.

(iii) Write SQL query to display ProductName, Manufacturer and Price for all products that are not given any discount.

(iv) Write SQL query to display ProductName and price for all products.

(v) Write SQL query to display ClientName, City, P_ID and ProductName for all clients whose city is Delhi.

(vi) Which column is used as Foreign Key and name the table where it has been used as Foreign Key?

Answer

(i)

SELECT ProductName, Price 
FROM PRODUCT
WHERE Price BETWEEN 50 AND 150;

+-------------+-------+
| ProductName | Price |
+-------------+-------+
| Bath Soap   |    55 |
| Face Wash   |    95 |
| Shampoo     |   120 |
+-------------+-------+

(ii)

SELECT * FROM PRODUCT
WHERE Manufacturer = 'XYZ' OR Manufacturer = 'ABC';

+------+-------------+--------------+-------+----------+
| P_ID | ProductName | Manufacturer | Price | Discount |
+------+-------------+--------------+-------+----------+
| BS01 | Bath Soap   | ABC          |    55 |     NULL |
| FW05 | Face Wash   | ABC          |    45 |        5 |
| FW12 | Face Wash   | XYZ          |    95 |     NULL |
| SH06 | Shampoo     | XYZ          |   120 |       10 |
+------+-------------+--------------+-------+----------+

(iii)

SELECT ProductName, Manufacturer, Price
FROM PRODUCT
WHERE Discount IS NULL;

+---------------+--------------+-------+
| ProductName   | Manufacturer | Price |
+---------------+--------------+-------+
| Bath Soap     | ABC          |    55 |
| Face Wash     | XYZ          |    95 |
| Talcum Powder | LAK          |    40 |
+---------------+--------------+-------+

(iv)

SELECT ProductName, Price
FROM PRODUCT;

+---------------+--------+
| productname   | price  |
+---------------+--------+
| Bath Soap     |  55.00 |
| Face Wash     |  45.00 |
| Face Wash     |  95.00 |
| Shampoo       | 120.00 |
| Talcum Powder |  40.00 |
+---------------+--------+

(v)

SELECT C.ClientName, C.City, P.P_ID, P.ProductName 
FROM PRODUCT P, CLIENT C 
WHERE P.P_ID = C.P_ID AND C.CITY = 'DELHI';

+---------------+-------+------+---------------+
| ClientName    | City  | P_ID | ProductName   |
+---------------+-------+------+---------------+
| Cosmetic Shop | Delhi | TP01 | Talcum Powder |
| Live Life     | Delhi | BS01 | Bath Soap     |
| Pretty Woman  | Delhi | SH06 | Shampoo       |
| Dreams        | Delhi | FW12 | Face Wash     |
+---------------+-------+------+---------------+

(vi) The column used as a Foreign Key is P_ID in the CLIENT table, and it references the P_ID column in the PRODUCT table.

Answer the questions based on the table given below:

Table:HOSPITAL

(i) To list the names of all the patients admitted after 15/01/98.

(ii) To list the names of female patients who are in ENT department.

(iii) To list names of all patients with their date of admission.

(iv) To display Patient’s Name, Charges and Age for only female patients.

Answer

(i)

SELECT NAME
FROM HOSPITAL
WHERE DATOFADM > '1998-01-15';

+--------+
| NAME   |
+--------+
| Arpit  |
| Kareem |
| Ketaki |
| Ankita |
| Zareen |
| Shilpa |
+--------+

(ii)

SELECT NAME
FROM HOSPITAL
WHERE SEX = 'F' AND DEPARTMENT = 'ENT';

+---------+
| NAME    |
+---------+
| Zareena |
| Ketaki  |
+---------+

(iii)

SELECT NAME, DATOFADM
FROM HOSPITAL;

+---------+------------+
| name    | dateofadm  |
+---------+------------+
| Arpit   | 1998-01-21 |
| Zareena | 1997-12-12 |
| Kareem  | 1998-02-19 |
| Arun    | 1998-01-11 |
| Zubin   | 1998-01-12 |
| Ketaki  | 1998-02-24 |
| Ankit   | 1998-02-20 |
| Zareen  | 1998-02-22 |
| Kush    | 1998-01-13 |
| Shilpa  | 1998-02-21 |
+---------+------------+

(iv)

SELECT NAME, CHARGES, AGE
FROM HOSPITAL
WHERE SEX = 'F';

+---------+---------+-----+
| NAME    | CHARGES | AGE |
+---------+---------+-----+
| Zareena |     250 |  22 |
| Ketaki  |     250 |  16 |
| Ankita  |     800 |  29 |
| Zareen  |     300 |  45 |
| Shilpa  |     400 |  23 |
+---------+---------+-----+