Relational Database and SQL

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

MySQL provides a dummy table named Dual.

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

Patterns in MySQL are described using two special wild card characters such as % (percent) and _ (underscore).

The keyword NOT LIKE is used to select rows that do not match the specified pattern of characters.

The default order of ORDER BY clause is Ascending.

The COUNT() function is used to count the number of records in a column.

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

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

To specify filtering condition for groups, the HAVING clause is used in MySQL.

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 a similar type of data.

MySQL supports different platforms like UNIX and Windows.

Answer

True

Reason — MySQL supports different platforms like UNIX and Windows because it is an open-source and freely-available Relational Database Management System (RDBMS).

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.

SELECT MIN (Salary) FROM Employee will return the highest salary from the table.

Answer

False

Reason — SELECT MIN (Salary) FROM Employee will return the lowest salary from the Employee table.

Group functions can be applied to any numeric values, some text types and DATE values.

Answer

True

Reason — Group functions, also known as aggregate functions in SQL, can be applied to numeric values, some text types, and DATE values. These functions perform calculations across multiple rows and return a single result.

The ............... allows us 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 us 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. Keyword
2. Literal
3. Variable
4. Table

Answer

Keyword

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

Which 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>;.

Which of these commands helps to fetch data from 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.

............... is known as range operator in MySQL.

1. IN
2. DISTINCT
3. IS
4. BETWEEN

Answer

BETWEEN

Reason — The BETWEEN operator is known as the range operator in MySQL because it defines a range of values that the column value must fall within for the condition to be true.

The ............... clause allows sorting of query results by one or more columns.

1. ALL
2. DISTINCT
3. GROUP BY
4. ORDER BY

Answer

ORDER BY

Reason — The SQL ORDER BY clause is used to sort the result-set in ascending or descending order by one or more columns in a table.

Which clause is used in query to place the condition on groups in MySQL?

1. WHERE
2. HAVING
3. GROUP BY
4. Both (i) & (ii)

Answer

HAVING

Reason — The HAVING clause is used in SQL queries to place conditions on groups when using the GROUP BY clause.

Which of the following is a DDL command?

1. SELECT
2. ALTER
3. INSERT
4. UPDATE

Answer

ALTER

Reason — DDL (Data Definition Language) commands in SQL are used to create and define tables and other database objects. Examples of DDL commands include ALTER, which is used to modify objects like tables, indexes, views, and constraints. On the other hand, SELECT, INSERT, and UPDATE commands are part of DML (Data Manipulation Language), used for retrieving, inserting, and updating data within the database.

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

1. Unique
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.

............... command is used to remove primary key from a table in SQL.

1. Update
2. Remove
3. Alter
4. Drop

Answer

Alter

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

Which of the following commands will delete the table from MYSQL database?

1. DELETE TABLE
2. DROP TABLE
3. REMOVE TABLE
4. ALTER TABLE

Answer

DROP TABLE

Reason — The DROP TABLE command in SQL will delete the table from the MYSQL database. Once this command is executed, the table and all its associated data are removed from the database. After dropping the table, the table name is no longer recognized within the database system, and no further commands can be executed on that object.

Which function is used to display the total number of records from a table in a database?

1. Sum(*)
2. Total(*)
3. Count(*)
4. Return(*)

Answer

Count(*)

Reason — The Count(*) function counts the total number of records/rows satisfying the condition along with NULL values, if any, in the table.

With SQL, how do you select all the records from a table named "Persons", where the value of the column "FirstName" ends with an "a"?

1. SELECT * FROM Persons WHERE FirstName = 'a';
2. SELECT * FROM Persons WHERE FirstName LIKE 'a%';
3. SELECT * FROM Persons WHERE FirstName LIKE '%a';
4. SELECT * FROM Persons WHERE FirstName = '%a%';

Answer

SELECT * FROM Persons WHERE FirstName LIKE '%a';

Reason — The SQL query SELECT * FROM Persons WHERE FirstName LIKE '%a'; retrieves all records from the "Persons" table where the "FirstName" column ends with "a". The LIKE keyword with "%" as a wildcard matches any characters preceding "a".

Assertion (A): In hierarchical model, searching for a record is a time-consuming task.

Reasoning (R): Hierarchical model owns to the organization of information/data in a tree-like structure.

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 hierarchical model arranges data in a tree structure, where each record has a parent-child relationship. Searching for a specific record in such a model may require traversing through multiple levels of the hierarchy, which is time-consuming.

Assertion (A): RDBMS stands for Relational Database Management System.

Reasoning (R): RDBMS does not allow relating or associating two tables in a database.

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
RDBMS stands for Relational Database Management System. RDBMS allows relating or associating two tables in a database through the use of primary keys, foreign keys, and joins.

Assertion (A): A database is the largest component for holding and storing data and may contain several tables.

Reasoning (R): Each table comprises multiple rows and records.

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
A database is the largest component for storing and managing data, capable of containing multiple tables. Tables are utilized to organize and store related data in a structured manner, with each table consisting of multiple rows and columns.

Assertion (A): Referential integrity is a constraint that defines rules of holding data in parent and child table.

Reasoning (R): Referential integrity ensures that users don't accidently delete or change related data.

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
Referential integrity is a constraint that defines rules for maintaining data relationships between parent and child tables in a database. The referential integrity constraint requires that values in a foreign key column must either be present in the primary key that is referenced by the foreign key or they must be null. It ensures the preservation of related data by preventing accidental deletion or modification.

Assertion (A): The number of attributes or columns in a relation is called the degree of the relation.

Reasoning (R): The number of tuples or records in a relation is called the cardinality of the relation.

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
The degree of a relation and the cardinality of a relation are two separate concepts in database management. The number of attributes or columns in a relation is called the degree of the relation, while the number of tuples or records in a relation is called the cardinality of the relation.

Assertion (A): A foreign key is an attribute whose value is derived from the primary key of another relation.

Reasoning (R): A foreign key is used to represent the relationship between tables or relations.

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
A foreign key is a non-key attribute whose value is derived from the primary key of another table in a database. It references the primary key in another table, thereby establishing a relationship between the two tables.

Assertion (A): Order by clause is used to sort the records of a table in ascending or descending order.

Reasoning (R): For sorting in ascending or descending order, ASC and DESC keywords are used along with the Order by 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 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. The syntax for ORDER BY clause is as follows: SELECT <column-list> FROM <table_name> [WHERE <condition>] ORDER BY <column_name> [ASC|DESC];.

Assertion (A): COUNT() function ignores distinct values.

Reasoning (R): Distinct clause ignores duplicate values.

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
The COUNT() function in SQL does not ignore distinct values, it counts all occurrences, including distinct values. On the other hand, the DISTINCT keyword in SQL eliminates duplicate values, ensuring that only distinct values are considered.

Assertion (A): The HAVING clause is used with GROUP BY clause and aggregate functions.

Reasoning (R): WHERE clause specifies the condition on individual rows or records.

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
The HAVING clause is used in combination with the GROUP BY clause. It allows aggregate functions to be used along with the specified condition. This is necessary because aggregate functions cannot be used with the WHERE clause, which is evaluated on a single row, whereas aggregate functions are evaluated on a group of rows specified by the GROUP BY clause.

Assertion (A): SQL queries are performed on a table created under a database.

Reasoning (R): The database is a folder created by the user in MySQL workbench. Thereafter, the tables are created in a database where various queries can be performed using SQL commands.

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 database is a folder created by the user in MySQL workbench. Within this database, tables are created, and SQL commands are used to perform various queries on these tables, such as altering tables, inserting data, and retrieving data.

Assertion (A): Cartesian product of two sets (A and B) is defined as the set of all possible ordered pairs denoted by (A + B).

Reasoning (R): The Cartesian product is also known as the cross product of two sets. The Cartesian product of two tables can be evaluated such that each row of the first table will be paired with all the rows in the second 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

A is false but R is true.

Explanation
The Cartesian product of two sets A and B is defined as the set of all possible ordered pairs, denoted as A × B. It is also known as the cross product or cross-join of two sets. In the context of databases, the Cartesian product of two tables can be evaluated such that each row of the first table is paired with all the rows in the second table.

Assertion (A): Aggregate functions in SQL operate on multiple set of values and return a single value as the output.

Reasoning (R): The aggregate functions are used to perform operations on a group of values. Some common aggregate functions are SUM(), MAX(), MIN(), etc.

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
Aggregate functions (multiple-row functions) in SQL operate on multiple sets of values and return a single value as the output. Some common aggregate functions are SUM(), MAX(), MIN(), etc., which are used to perform operations on groups of values.

What is an Alternate Key?

Answer

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

Write some characteristics of RDBMS.

Answer

The characteristics of Relational Database Management System (RDBMS) are as follows:

1. Structured Data Storage — RDBMS organizes data into structured tables consisting of rows and columns.
2. Data Integrity — RDBMS enforces data integrity through constraints such as primary keys, foreign keys, unique constraints, and check constraints.
3. Relationships — RDBMS supports relationships between tables.
4. SQL Support — RDBMS uses SQL (Structured Query Language) as the standard language for querying and manipulating data.

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 a 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 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

Give the terms for each of the following:

(a) Collection of logically related records.

(b) The fundamental data storage unit in a relational database.

(c) Attribute that can uniquely identify the tuples in a relation.

(d) Special value that is stored when actual data value is unknown for an attribute.

(e) An attribute which can uniquely identify tuples of the table but is not defined as primary key of the table.

(f) Software that is used to create, manipulate and maintain a relational database.

Answer

(a) Table

(b) Table

(c) Primary key

(d) NULL value

(e) Alternate Key

(f) Relational Database Management System (RDBMS)

An organization wants to create two tables EMP & DEPENDENT to maintain the following details about its employees and their dependents.

EMPLOYEE(AadhaarNumber, Name, Address, Department, EmployeeID)
DEPENDENT(EmployeeID, DependentName, Relationship)

(a) Name the attributes of EMPLOYEE, which can be used as candidate keys.

(b) The company wants to retrieve details of dependents of a particular employee. Name the tables and the key which are required to retrieve these detail.

(c) What is the degree of EMPLOYEE and DEPENDENT relation?

Answer

(a) In the EMPLOYEE table, the attributes AadharNumber and EmployeeID can be used as candidate keys. This means that either AadharNumber or EmployeeID can uniquely identify each record in the EMPLOYEE table.

(b) The EMPLOYEE and DEPENDENT tables are linked using the EmployeeID key, which is utilized to retrieve details of dependents associated with a specific employee.

(c) In the EMPLOYEE relation, there are five attributes, resulting in a degree of 5. Similarly, the DEPENDENT relation has three attributes, making its degree 3.

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

CONCAT() function 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,

SELECT 13 * 15;

Which keywords eliminate redundant data from a query?

Answer

DISTINCT keyword eliminates the redundant data from a query result.

What is the significance of GROUP BY clause in an SQL query?

Answer

The GROUP BY clause in SQL can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns. It groups the rows on the basis of the values present in one of the columns and then the aggregate functions are applied on any column of these groups to obtain the result of the query.

What is the difference between WHERE and HAVING clause in SQL select command?

Answer

The WHERE clause in an SQL SELECT command is used to filter individual rows based on specified conditions, such as column values, before any grouping is performed. On the other hand, the HAVING clause is used for filtering groups of rows after grouping has been applied, with aggregate functions like SUM or AVG.

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

(i) Display those records from table PRODUCT where prod_id is more than 100.

(ii) List records from table PRODUCT where prod_name is 'Almirah'.

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

(iv) Display the product names whose price is less than the average of price.

(v) Show the total number of records in the table PRODUCT.

Answer

(i)

SELECT * FROM PRODUCT
WHERE prod_id > 100;

(ii)

SELECT * FROM PRODUCT
WHERE prod_name = 'Almirah';

(iii)

SELECT * FROM PRODUCT
WHERE price BETWEEN 200 AND 500; 

(iv)

SELECT prod_name
FROM PRODUCT
WHERE price < (SELECT AVG(price) FROM PRODUCT);

(v)

SELECT COUNT(*) AS total_records 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.

Define foreign key.

Answer

A non-key attribute, whose values are derived from the primary key of some other table, is known as foreign key in its current table.

Differentiate between Primary key and Unique constraints.

Answer

Consider the following EMP and DEPT tables:

Table: EMP

Table: DEPT

Write the SQL command to get the following:

(a) Show the minimum, maximum and average salary of managers.

(b) Count the number of clerks in the organization.

(c) Display the designation-wise list of employees with name, salary and date of joining.

(d) Count the number of employees who are not getting commission.

(e) Show the average salary for all departments having salary > 2000.

(f) List the count of employees grouped by DeptID.

(g) Display the maximum salary of employees in each department.

(h) Display the name of employees along with their designation and department name.

(i) Count the number of employees working in ACCOUNTS department.

Answer

(a)

SELECT MIN(Sal) AS MinSalary, MAX(Sal) AS MaxSalary, AVG(Sal) AS AvgSalary
FROM EMP
WHERE Designation = 'MANAGER';

+-----------+-----------+-----------+
| MinSalary | MaxSalary | AvgSalary |
+-----------+-----------+-----------+
|      2985 |      2985 |      2985 |
+-----------+-----------+-----------+

(b)

SELECT COUNT(*) AS ClerkCount
FROM EMP
WHERE Designation = 'CLERK';

+------------+
| ClerkCount |
+------------+
|          1 |
+------------+

(c)

SELECT Designation, EmpName, Sal, DOJ
FROM EMP
ORDER BY Designation;

+-------------+-----------+------+------------+
| Designation | EmpName   | Sal  | DOJ        |
+-------------+-----------+------+------------+
| CLERK       | SMITH     |  800 | 1990-12-18 |
| MANAGER     | MAHADEVAN | 2985 | 1991-04-02 |
| SALESMAN    | ANYA      | 1600 | 1991-02-20 |
| SALESMAN    | SETH      | 1250 | 1991-02-22 |
+-------------+-----------+------+------------+

(d)

SELECT COUNT(*) AS No_comm
FROM EMP
WHERE comm is NULL;

+---------+
| No_comm |
+---------+
|       2 |
+---------+

(e)

SELECT D.DeptName, AVG(E.Sal) AS AvgSalary
FROM EMP E, DEPT D
WHERE E.DeptID = D.DeptID AND E.Sal > 2000
GROUP BY D.DeptName;

+-----------+-----------+
| DeptName  | AvgSalary |
+-----------+-----------+
| PERSONNEL |      2985 |
+-----------+-----------+

(f)

SELECT DeptID, COUNT(*) AS EmpCount
FROM EMP
GROUP BY DeptID;

+--------+----------+
| DeptID | EmpCount |
+--------+----------+
|     20 |        2 |
|     30 |        2 |
+--------+----------+

(g)

SELECT D.DeptName, MAX(E.Sal) AS MaxSalary
FROM EMP E, DEPT D
WHERE E.DeptID = D.DeptID
GROUP BY D.DeptName;

+-----------+-----------+
| DeptName  | MaxSalary |
+-----------+-----------+
| PERSONNEL |      2985 |
| ACCOUNTS  |      1600 |
+-----------+-----------+

(h)

SELECT EMP.EMPNAME, EMP.DESIGNATION, DEPT.DEPTNAME
FROM EMP, DEPT
WHERE EMP.DEPTID = DEPT.DEPTID;

+-----------+-------------+-----------+
| EMPNAME   | DESIGNATION | DEPTNAME  |
+-----------+-------------+-----------+
| SMITH     | CLERK       | PERSONNEL |
| ANYA      | SALESMAN    | ACCOUNTS  |
| SETH      | SALESMAN    | ACCOUNTS  |
| MAHADEVAN | MANAGER     | PERSONNEL |
+-----------+-------------+-----------+

(i)

SELECT COUNT(*) AS NUM_EMP
FROM EMP, DEPT
WHERE EMP.DEPTID = DEPT.DEPTID
AND DEPTNAME = 'ACCOUNTS';

+---------+
| NUM_EMP |
+---------+
|       2 |
+---------+

Write SQL commands for (i) to (vi) and write output for (vii) on the basis of PRODUCTS relation given below:

Table: PRODUCTS

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

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

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

(iv) To show number of products from each company.

(v) To count the number of PRODUCTS which shall be out of warranty on 20-NOV-2020.

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

(vii) Give the output of the following statements:

(a) SELECT COUNT(DISTINCT COMPANY) FROM PRODUCTS;

(b) SELECT MAX(PRICE) FROM PRODUCTS WHERE WARRANTY <= 3;

Answer

(i)

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 |
+-------+-------+---------+-------+-------+-------------+----------+

(ii)

SELECT DISTINCT COMPANY
FROM PRODUCTS
WHERE WARRANTY > 2;

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

(iii)

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

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

(iv)

SELECT COMPANY, COUNT(*) AS ProductCount
FROM PRODUCTS
GROUP BY COMPANY;

+---------+--------------+
| COMPANY | ProductCount |
+---------+--------------+
| BPL     |            1 |
| SONY    |            2 |
| LENOVO  |            1 |
| COMPAQ  |            1 |
+---------+--------------+

(v)

SELECT COUNT(*)
FROM PRODUCTS
WHERE DATE_ADD(MANUFACTURE, INTERVAL WARRANTY YEAR) <= '2020-11-20';

+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

(vi)

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.

(vii)

(a)

+-------------------------+
| COUNT(DISTINCT COMPANY) |
+-------------------------+
|                       4 |
+-------------------------+

(b)

+------------+
| MAX(PRICE) |
+------------+
|      39000 |
+------------+

Write SQL commands for (i) to (vi) on the basis of relations given below:

Table: BOOKS

Table: ISSUED

(i) To show the books of "FIRST PUBL" Publishers written by P.Purohit.

(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 bookname and price of the books, more than 3 copies of which have been issued.

(v) To show total cost of books of each type.

(vi) To show the details of the costliest book.

Answer

(i)

SELECT BOOK_NAME
FROM BOOKS
WHERE PUBLISHERS = "FIRST PUBL" AND AUTHOR_NAME = "P.PUROHIT";

There is no output produced because there are no books published by "FIRST PUBL" and written by "P.Purohit" in the table BOOKS.

(ii)

SELECT SUM(Price * qty) AS Cost
FROM BOOKS
WHERE Publishers = 'FIRST PUBL'
GROUP BY publishers;

+-------+
| Cost  |
+-------+
| 28620 |
+-------+

(iii)

UPDATE BOOKS 
SET Price = Price - (Price * 5/100)  
WHERE Publishers = 'EPB';

(iv)

SELECT b.Book_name, b.Price
FROM BOOKS b, ISSUED i 
WHERE b.Book_ID = i.Book_ID and i.Qty_Issued > 3;

+------------------------------+-------+
| Book_name                    | Price |
+------------------------------+-------+
| Computer Science with python |   655 |
| Computer Science with python |   550 |
| Concise Mathematics          |   600 |
+------------------------------+-------+

(v)

SELECT Type, SUM(Price * qty) AS Total_Cost
FROM BOOKS
GROUP BY Type;

+-------+------------+
| Type  | Total_Cost |
+-------+------------+
| Prog  |      59070 |
| Maths |       6000 |
| Comp  |      18120 |
+-------+------------+

(vi)

SELECT *
FROM BOOKS
WHERE Price = (SELECT MAX(Price) FROM BOOKS);

+---------+-------------------+-------------+------------+-------+------+-----+
| book_id | book_name         | author_name | publishers | price | type | qty |
+---------+-------------------+-------------+------------+-------+------+-----+
| P001    | Computer Networks | B.Agarwal   | FIRST PUBL |   755 | Comp |  24 |
+---------+-------------------+-------------+------------+-------+------+-----+

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

Write SQL Commands for (i) to (v) and write the outputs for (vi) to (viii) on the basis of the following table:

Table: FURNITURE

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

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

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

(iv) To display the price of 'Babycot'.

(v) To list item name, type and price of all items whose names start with 'D'.

(vi) SELECT DISTINCT Type FROM Furniture;

(vii) SELECT MAX(Price) FROM Furniture WHERE DateofStock > '2002-02-15' ;

(viii) SELECT COUNT(*) FROM Furniture WHERE Discount < 25 ;

Answer

(i)

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 |
+----+-------------+-------------+-------------+-------+----------+

(ii)

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

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

(iii)

DELETE FROM FURNITURE WHERE DISCOUNT = 30;

(iv)

SELECT PRICE
FROM FURNITURE
WHERE TYPE = 'BabyCot';

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

(v)

SELECT ITEM, TYPE, PRICE
FROM FURNITURE
WHERE ITEM LIKE 'D%';

+---------+-------------+-------+
| ITEM    | TYPE        | PRICE |
+---------+-------------+-------+
| Dolphin | BabyCot     |  9500 |
| Decent  | OfficeTable | 25000 |
| Donald  | BabyCot     |  6500 |
+---------+-------------+-------+

(vi) SELECT DISTINCT Type FROM Furniture;

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

(vii) SELECT MAX(Price) FROM Furniture WHERE DateofStock > '2002-02-15' ;

+------------+
| MAX(Price) |
+------------+
|       9500 |
+------------+

(viii) SELECT COUNT(*) FROM Furniture WHERE Discount < 25 ;

+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

Write SQL Commands/output for the following on the basis of the given table GRADUATE:

Table: GRADUATE

(i) List the names of those students who have obtained rank 1 sorted by NAME.

(ii) Display a list of all those names whose AVERAGE is greater than 65.

(iii) Display the names of those students who have opted COMPUTER as a SUBJECT with an AVERAGE of more than 60.

(iv) List the names of all the students in alphabetical order.

(v) SELECT * FROM GRADUATE WHERE NAME LIKE "%I%";

(vi) SELECT DISTINCT RANK FROM GRADUATE;

Answer

(i)

SELECT NAME
FROM GRADUATE
WHERE `RANK` = 1
ORDER BY NAME;

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

(ii)

SELECT NAME
FROM GRADUATE
WHERE AVERAGE > 65;

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

(iii)

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

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

(iv)

SELECT NAME
FROM GRADUATE
ORDER BY NAME;

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

(v) SELECT * FROM GRADUATE WHERE NAME LIKE "%I%";

+-------+-------+---------+-----------+---------+------+
| S.No. | name  | stipend | subject   | average | rank |
+-------+-------+---------+-----------+---------+------+
|     4 | DIVYA |     350 | CHEMISTRY |      63 |    1 |
|     8 | LIZA  |     450 | COMPUTER  |      68 |    1 |
|    10 | NISHA |     300 | COMPUTER  |      57 |    2 |
+-------+-------+---------+-----------+---------+------+

(vi) 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 DISTINCT `RANK` FROM GRADUATE;

+------+
| RANK |
+------+
|    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 giving any discount.

(iv) Write SQL query to display ProductName and price for all products whose ProductName ends with 'h'.

(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
WHERE ProductName LIKE '%h';

+-------------+-------+
| ProductName | Price |
+-------------+-------+
| Face Wash   |    45 |
| Face Wash   |    95 |
+-------------+-------+

(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

(a) To list the names of all the patients admitted after 1998-01-15.

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

(c) To list the names of all patients with their date of admission in ascending order.

(d) To display Patient's Name, Charges, Age for only female patients.

(e) Find out the output of the following SQL commands:

1. SELECT COUNT(DISTINCT Charges) FROM HOSPITAL;
2. SELECT MIN(Age) FROM HOSPITAL WHERE Sex = "F";

Answer

(a)

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

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

(b)

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

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

(c)

SELECT NAME, DATEOFADM
FROM HOSPITAL
ORDER BY DATEOFADM;

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

(d)

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

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

(e)

1. SELECT COUNT(DISTINCT Charges) FROM HOSPITAL;

+-------------------------+
| COUNT(DISTINCT Charges) |
+-------------------------+
|                       5 |
+-------------------------+

2. SELECT MIN(Age) FROM HOSPITAL WHERE Sex = "F";

+----------+
| MIN(Age) |
+----------+
|       16 |
+----------+

A department store MyStore is considering to maintain their inventory using SQL to store the data. As a database administrator, Abhay has decided that:

• Name of the database — mystore
• Name of the table — STORE

The attributes of STORE are as follows:

ItemNo — numeric
ItemName — character of size 20
Scode — numeric
Quantity — numeric

Table: STORE

(a) Identify the attribute best suitable to be declared as a primary key.

(b) Write the degree and cardinality of the table STORE.

(c) Insert the following data into the attributes ItemNo, ItemName and SCode respectively in the given table

STORE.ItemNo = 2010, ItemName = "Note Book" and Scode = 25.

(d) Abhay wants to remove the table STORE from the database MyStore. Which command will he use from the following?

1. DELETE FROM STORE;
2. DROP TABLE STORE;
3. DROP DATABASE MYSTORE;
4. DELETE STORE FROM MYSTORE;

(e) Now Abhay wants to display the structure of the table STORE, i.e., name of the attributes and their respective data types that he has used in the table. Write the query to display the same.

Answer

(a) ItemNo attribute is best suitable to be declared as a primary key as it uniquely identifies each item in the inventory.

(b) The degree of the table STORE is 4, and the cardinality of the table STORE is 7.

(c)

INSERT INTO STORE(ItemNo, ItemName, Scode)
VALUES(2010, 'Note Book', 25);

(d) DROP TABLE STORE;
Reason — DROP TABLE command is used to remove/delete a table permanently. The syntax is : DROP TABLE <table_name>;. Hence, according to this DROP TABLE STORE; is the correct command to remove the STORE table from the database MyStore.

(e)

DESCRIBE STORE;

+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| ItemNo   | int      | NO   | PRI | NULL    |       |
| ItemName | char(20) | YES  |     | NULL    |       |
| Scode    | int      | YES  |     | NULL    |       |
| Quantity | int      | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+