Loading...
Please wait while we prepare your content
Please wait while we prepare your content
Solutions for Computer Science, Class 12, CBSE
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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"?
SELECT * FROM Persons WHERE FirstName = 'a';
SELECT * FROM Persons WHERE FirstName LIKE 'a%';
SELECT * FROM Persons WHERE FirstName LIKE '%a';
SELECT * FROM Persons WHERE FirstName = '%a%';
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".
The characteristics of Relational Database Management System (RDBMS) are as follows:
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.
SQL (Structured Query Language) is a standard language for accessing and manipulating databases.
The different categories of SQL commands are as follows:
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.
Data Definition Language (DDL) | Data Manipulation Language (DML) |
---|---|
DDL provides a set of definitions to specify the storage structure and access methods used by the database system. | DML is a language that enables users to access or manipulate data as organized by the appropriate data model. |
DDL commands are used to perform tasks such as creating, altering, and dropping schema objects. They are also used to grant and revoke privileges and roles, as well as for maintenance commands related to tables. | DML commands are used to retrieve, insert, delete, modify data stored in the database. |
Examples of DDL commands are CREATE, ALTER, DROP, GRANT, ANALYZE etc. | Examples of DML commands are INSERT, UPDATE, DELETE, SELECT etc. |
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.
(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?
(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.
Char datatype | Varchar datatype |
---|---|
Char datatype specifies a fixed length string. | Varchar datatype specifies a variable length string. |
Defining a length is not required, but the default is 1. | Defining a length is required. |
CHAR(n) ensures that all values stored in that column are of length n bytes, padding shorter values with blanks while maintaining a fixed size of n bytes. | VARCHAR(n) columns have a maximum size of n bytes, storing values exactly as specified without adding blanks for shorter lengths. Exceeding n bytes results in an error message. |
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.
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.
(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;
Primary key | Unique constraints |
---|---|
A Primary key is a set of one or more attributes/fields which uniquely identifies a tuple/row in a table. | UNIQUE constraint ensures that no two rows have the same value in the specified column(s). |
Primary Keys do not allow NULL values. | Unique Constraint allows NULL values. |
There can be only one Primary Key in a table. | Multiple Unique Constraints can exist in a table. |
Consider the following EMP and DEPT tables:
Table: EMP
EmpNo | EmpName | City | Designation | DOJ | Sal | Comm | DeptID |
---|---|---|---|---|---|---|---|
8369 | SMITH | Mumbai | CLERK | 1990-12-18 | 800.00 | NULL | 20 |
8499 | ANYA | Varanasi | SALESMAN | 1991-02-20 | 1600.00 | 300.00 | 30 |
8521 | SETH | Jaipur | SALESMAN | 1991-02-22 | 1250.00 | 500.00 | 30 |
8566 | MAHADEVAN | Delhi | MANAGER | 1991-04-02 | 2985.00 | NULL | 20 |
Table: DEPT
DeptID | DeptName | MgrID | Location |
---|---|---|---|
10 | SALES | 8566 | Mumbai |
20 | PERSONNEL | 9698 | Delhi |
30 | ACCOUNTS | 4578 | Delhi |
40 | RESEARCH | 8839 | Bengaluru |
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.
(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
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 |
(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;
(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
Book_ID | Book_name | Author_name | Publishers | Price | Type | qty |
---|---|---|---|---|---|---|
K0001 | Let us C | Y. Kanetkar | EPB | 450 | Prog | 15 |
P0001 | Computer Networks | B. Agarwal | FIRST PUBL | 755 | Comp | 24 |
M0001 | Mastering C++ | K.R. Venugopal | EPB | 165 | Prog | 60 |
N0002 | VC++ advance | P. Purohit | TDH | 250 | Prog | 45 |
K0002 | Programming with Python | Sanjeev | FIRST PUBL | 350 | Prog | 30 |
L02 | Computer Science with Python | Sumita Arora | Dhanpat rai | 655 | Prog | 16 |
L04 | Computer Science with Python | Preeti Arora | Sultan chand | 550 | Prog | 20 |
L05 | Concise Mathematics | R.K.Bansal | Selina | 600 | Maths | 10 |
Table: ISSUED
Book_ID | Qty_Issued |
---|---|
L02 | 13 |
L04 | 5 |
L05 | 21 |
(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.
(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 |
+---------+-------------------+-------------+------------+-------+------+-----+
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.
Primary key | Candidate key |
---|---|
A primary key is a set of one or more attributes/fields which uniquely identifies a tuple/row in a table. | A candidate key refers to all the attributes in a relation that are candidates or are capable of becoming a primary key. |
There can be only one primary key per table. | A table can have multiple candidate keys. Only one of them is chosen as the primary key. |
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.
Data Definition Language (DDL) | Data Manipulation Language (DML) |
---|---|
DDL provides a set of definitions to specify the storage structure and access methods used by the database system. | DML is a language that enables users to access or manipulate data as organized by the appropriate data model. |
DDL commands are used to perform tasks such as creating, altering, and dropping schema objects. They are also used to grant and revoke privileges and roles, as well as for maintenance commands related to tables. | DML commands are used to retrieve, insert, delete, modify data stored in the database. |
Examples of DDL commands are CREATE, ALTER, DROP, GRANT, ANALYZE etc. | Examples of DML commands are INSERT, UPDATE, DELETE, SELECT etc. |
Write SQL Commands for (i) to (v) and write the outputs for (vi) to (viii) on the basis of the following table:
Table: FURNITURE
NO | ITEM | TYPE | DATEOFSTOCK | PRICE | DISCOUNT |
---|---|---|---|---|---|
1 | White Lotus | DoubleBed | 2002-02-23 | 3000 | 25 |
2 | Pinkfeathers | BabyCot | 2002-01-29 | 7000 | 20 |
3 | Dolphin | BabyCot | 2002-02-19 | 9500 | 20 |
4 | Decent | OfficeTable | 2002-02-01 | 25000 | 30 |
5 | Comfortzone | DoubleBed | 2002-02-12 | 25000 | 30 |
6 | Donald | BabyCot | 2002-02-24 | 6500 | 15 |
(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 ;
(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
S.No. | NAME | STIPEND | SUBJECT | AVERAGE | RANK |
---|---|---|---|---|---|
1 | KARAN | 400 | PHYSICS | 68 | 1 |
2 | RAJ | 450 | CHEMISTRY | 68 | 1 |
3 | DEEP | 300 | MATHS | 62 | 2 |
4 | DIVYA | 350 | CHEMISTRY | 63 | 1 |
5 | GAURAV | 500 | PHYSICS | 70 | 1 |
6 | MANAV | 400 | CHEMISTRY | 55 | 2 |
7 | VARUN | 250 | MATHS | 64 | 1 |
8 | LIZA | 450 | COMPUTER | 68 | 1 |
9 | PUJA | 500 | PHYSICS | 62 | 1 |
10 | NISHA | 300 | COMPUTER | 57 | 2 |
(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;
(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 |
+------+
Alternate Key | Candidate Key |
---|---|
A candidate key refers to all the attributes in a relation that are candidates or are capable of becoming a primary key. | Any attribute which is capable of becoming a primary key but is not a primary key is called an alternate key. |
For the given table, do as directed:
Table: STUDENT
ColumnName | Data type | size | Constraint |
---|---|---|---|
ROLLNO | Integer | 4 | Primary Key |
SNAME | Varchar | 25 | Not Null |
GENDER | Char | 1 | Not Null |
DOB | Date | Not Null | |
FEES | Integer | 4 | Not Null |
HOBBY | Varchar | 15 | Null |
(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.
(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
P_ID | ProductName | Manufacturer | Price | Discount |
---|---|---|---|---|
TP01 | Talcum Powder | LAK | 40 | NULL |
FW05 | Face Wash | ABC | 45 | 5 |
BS01 | Bath Soap | ABC | 55 | NULL |
5H06 | Shampoo | XYZ | 120 | 10 |
FW12 | Face Wash | XYZ | 95 | NULL |
Table: CLIENT
C_ID | ClientName | City | P_ID |
---|---|---|---|
01 | Cosmetic Shop | Delhi | TP01 |
02 | Total Health | Mumbai | FW05 |
03 | Live Life | Delhi | BS01 |
04 | Pretty Woman | Delhi | 5H06 |
05 | Dreams | Delhi | FW12 |
(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?
(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
S.No. | Name | Age | Department | Dateofadm | Charges | Sex |
---|---|---|---|---|---|---|
1 | Arpit | 62 | Surgery | 1998-01-21 | 300 | M |
2 | Zareena | 22 | ENT | 1997-12-12 | 250 | F |
3 | Kareem | 32 | Orthopaedic | 1998-02-19 | 200 | M |
4 | Arun | 12 | Surgery | 1998-01-11 | 300 | M |
5 | Zubin | 30 | ENT | 1998-01-12 | 250 | M |
6 | Ketaki | 16 | ENT | 1998-02-24 | 250 | F |
7 | Ankit | 29 | Cardiology | 1998-02-20 | 800 | F |
8 | Zareen | 45 | Gynaecology | 1998-02-22 | 300 | F |
9 | Kush | 19 | Cardiology | 1998-01-13 | 800 | M |
10 | Shilpa | 23 | Nuclear Medicine | 1998-02-21 | 400 | F |
(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:
(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
ItemNo | ItemName | Scode | Quantity |
---|---|---|---|
2005 | Sharpener Classic | 23 | 60 |
2003 | Ball Pen 0.25 | 22 | 50 |
2002 | Gel Pen Premium | 21 | 150 |
2006 | Gel Pen Classic | 21 | 250 |
2001 | Eraser Small | 22 | 220 |
2004 | Eraser Big | 22 | 110 |
2009 | Ball Pen 0.5 | 21 | 180 |
(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?
(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.
(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 | |
+----------+----------+------+-----+---------+-------+
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);
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.