Loading...
Please wait while we prepare your content
Please wait while we prepare your content
Solutions for Informatics Practices, Class 12, CBSE
Assertion (A): Database management system is an application software which arranges data in a well-organized manner in the form of tables.
Reasoning (R): DBMS acts as an interface between the database stored in the computer memory and the user.
Both A and R are true and R is the correct explanation of A.
Explanation
Database Management System (DBMS) is an application software that acts as an interface between the database stored in the computer memory and the user. It enables users to interact with the database and store data in a well-organized manner in the form of tables. The purpose of the DBMS software is to allow the user to create, access, modify, and control a database.
Assertion (A): A database consists of multiple tables.
Reasoning (R): A foreign key is used to represent the relationship between two tables.
Both A and R are true and R is the correct explanation of A.
Explanation
In a database system, data is organized into multiple tables for better organization, management, and query efficiency. 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): The limitations of traditional file system are overcome by storing data in a database.
Reasoning (R): We can organize related data logically in a database.
Both A and R are true and R is the correct explanation of A.
Explanation
Traditional file systems have limitations such as data redundancy, inconsistency, and difficulties in data sharing and security. In contrast, databases store data in a structured and organized manner, which helps overcome these limitations. They allow us to logically organize related data, making data management, retrieval, and manipulation more efficient.
Assertion (A): The number of attributes in a relation is called the degree of the relation.
Reasoning (R): The number of tuples 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): Each table must have one primary key.
Reasoning (R): Primary key is a set of one or more attributes that uniquely identifies a tuple in a relation.
Both A and R are true and R is the correct explanation of A.
Explanation
In a database, each table can have only one primary key, which is unique and non-redundant in nature. The primary key is a set of one or more attributes or fields that uniquely identify a tuple or row in a relation.
Assertion (A): A database can have only one table.
Reasoning (R): If a piece of data is stored in two places in a database, then it leads to wastage of storage space.
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.
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.
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.
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 only works with numeric data type only.
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): FLOAT and DOUBLE are data types.
Reasoning (R): Both can hold any number up to 23 digits.
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.
Kunal has entered the following SQL command in the table 'STUDENT' that has TotalMarks as one of the columns:
SELECT * FROM Student; #Statement-1
The total number of rows displayed is 20.
Then Kunal enters the following command:
SELECT * FROM STUDENT WHERE TotalMarks < 100; #Statement-2
The number of rows displayed is 15.
Kunal then enters the following command:
SELECT * FROM STUDENT WHERE TotalMarks >= 100; #Statement-3
He predicts the output of the above query as 5. Do you agree with Kunal? Give reasons for your answer.
I disagree with Kunal's prediction. Since Statement-1 returns all the rows and columns of the table Student i.e., 20, and Statement-2 returns 15 rows with TotalMarks less than 100, it means 20 - 15 = 5 students have TotalMarks greater than 100. However, Statement-3 returns rows where TotalMarks is greater than or equal to 100, which includes students who scored exactly 100, and we don't know how many students scored exactly 100, so the number of rows returned by Statement-3 will be greater than or equal to 5, but not necessarily exactly 5.
Mr. Shivaya is using a table 'COURSE' with the following columns: COURSE_ID, COURSE_NAME. He needs to display the names of all the courses which end with "SCIENCE". He has written the query mentioned below, which is not giving the desired result.
SELECT COURSE_ID, COURSE_NAME FROM COURSE WHERE COURSE_NAME = '_SCIENCE';
Help Mr. Shivaya to write the correct query.
SELECT COURSE_ID, COURSE_NAME FROM COURSE WHERE COURSE_NAME LIKE '%SCIENCE';
The "=" operator is used for exact matching and the "_" wildcard character in SQL is used to match a single character, not a sequence of characters. To achieve the desired result, Mr. Shivaya should use the LIKE
operator with the "%" wildcard character, which matches any sequence of characters.
Ms. Manisha, a veterinarian, created a table 'VETERINARY' with the following columns:
ANIMAL_ID, VACCINATION_DATE, ANIMAL, OWNER_NAME
She wants to see the details of all the animals other than Dog and Cat which she has vaccinated.
She has written the following query:
SELECT * FROM VETERINARY WHERE ANIMAL NOT IN ('DOG', 'CAT');
Write a suitable alternate query for producing the same result.
SELECT * FROM VETERINARY
WHERE ANIMAL != 'DOG' AND ANIMAL != 'CAT';
Your school management has decided to organize cricket matches between students of Classes XI and XII. All the students are divided into four teams—Team Rockstars, Team BigGamers, Team Magnet and Team Current. During the summer vacations, various matches are to be held between these teams. Help your sports teacher do the following:
(a) Create a database "Sports" and open it for creating table.
(b) Create a table "Team" with the following considerations:
(c) Show the structure of the table Team using SQL command.
(d) As per the preferences of the students, four teams were formed as given below.
Insert these four rows in Team table:
Row 1: (1, Team Rockstars)
Row 2: (2, Team BigGamers)
Row 3: (3, Team Magnet)
Row 4: (4, Team Current)
(e) Display the table Team.
(a)
CREATE DATABASE SPORTS;
USE SPORTS;
(b)
CREATE TABLE TEAM(TEAMID INT(9) Primary key, TEAMNAME VARCHAR(30));
(c)
DESC TEAM;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| TEAMID | int | NO | PRI | NULL | |
| TEAMNAME | varchar(30) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
(d)
INSERT INTO TEAM VALUES(1, "TEAM ROCKSTARS");
INSERT INTO TEAM VALUES(2, "TEAM BIGGAMERS");
INSERT INTO TEAM VALUES(3, "TEAM MAGNET");
INSERT INTO TEAM VALUES(4, "TEAM CURRENT");
(e)
SELECT * FROM TEAM;
+--------+----------------+
| TEAMID | TEAMNAME |
+--------+----------------+
| 1 | TEAM ROCKSTARS |
| 2 | TEAM BIGGAMERS |
| 3 | TEAM MAGNET |
| 4 | TEAM CURRENT |
+--------+----------------+
What is a database?
Organized collection of data or information that can be accessed, updated and managed
Reason — Database is an organized collection of interrelated data or information that can be accessed, updated and managed.
Primary Key
Reason — A primary key is a set of one or more attributes/fields which uniquely identifies a tuple/row in a table.
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.
(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.
(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.
Rajveer wants to rename column in display result for his query. Select the appropriate query for the same:
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.
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 ( RollNumber, 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') ;
1.
RollNumber | SName | SDateofBirth | GUID |
---|---|---|---|
1 | Atharv | 2003-05-15 | 12354899 |
2 | Sudha | 2002-02-28 | NULL |
2.
RollNumber | SName | SDateofBirth | GUID |
---|---|---|---|
1 | Atharv | 2003-05-15 | 12354899 |
2 | Sudha | 2002-02-28 | 00000000 |
3.
RollNumber | SName | SDateofBirth | GUID |
---|---|---|---|
1 | Atharv | 2003-05-15 | 12354899 |
2 | Sudha | 2002-02-28 | 0 |
4.
RollNumber | SName | SDateofBirth | GUID |
---|---|---|---|
1 | Atharv | 2003-05-15 | 12354899 |
2 | Sudha | 2002-02-28 |
RollNumber | SName | SDateofBirth | GUID |
---|---|---|---|
1 | Atharv | 2003-05-15 | 12354899 |
2 | Sudha | 2002-02-28 | NULL |
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.
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.
Information is processed, organized, and meaningful data that has been analyzed, interpreted, and structured to provide context, relevance, and value. It is derived from data through analysis and is used to make decisions or gain insights.
Data | Information |
---|---|
Data refers to raw facts, figures, or values that are collected and stored without any specific context or interpretation. It is unprocessed and lacks meaning on its own. | Information is processed, organized, and meaningful data that has been analyzed, interpreted, and structured to provide context, relevance, and value. It is derived from data through analysis and is used to make decisions or gain insights. |
Data is unstructured, discrete. | Information is structured, organized. |
Database is an organized collection of interpreted data that serves many applications.
A Database Management System is a general-purpose software system that facilitates the process of defining, constructing and manipulating databases for various applications.
A database system in SQL consists of tables with rows and columns, a schema defining structure and relationships, SQL for interaction, indexes for performance, constraints for data integrity.
A Database Management System (DBMS) 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.
Database | File |
---|---|
Database is an organized collection of interpreted data that serves many applications. | A file is a collection of unorganized data stored in a single location. |
Data retrieval is fast and efficient, as databases use indexing and querying mechanisms. | Data retrieval is slow and inefficient, as the entire file needs to be searched. |
Data sharing is easy, as multiple users and applications can access the database simultaneously. | Data sharing is limited, as files are typically accessed by a single user or application. |
Data integrity is ensured through constraints, triggers, and transactions. | Data integrity is not ensured, as data can be corrupted or lost easily. |
Databases support relationships between data entities. | Files do not support relationships between data entities. |
Data | File |
---|---|
Data refers to raw facts, figures, or values that are collected and stored without any specific context or interpretation. | A file is a collection of unorganized data stored in a single location. |
The purpose of data is to convey information, provide insights, or support decision-making. | The purpose of a file is to store, manage, and provide access to the data. |
Data can exist in various formats, such as numbers, text, images, or audio, and can be structured or unstructured. | A file has a specific format and structure, defined by its file type (e.g., .txt, .jpg, .mp3), which determines how the data is stored and retrieved. |
Suppose all customers of a particular business live in states for which the city name is unique. Given the following description for customer data:
CUST-ID, CUST-NAME, STREET, CITY, STATE, PHONE
(i) List the most likely key for the primary key.
(ii) List all the candidate keys and alternate keys.
(i) The most likely primary key is CUST-ID because it is a unique identifier assigned to each customer.
(ii) CUST-ID, CITY attributes can be considered as candidate keys as both are unique to each customer.
CITY can serve as an alternate key because all customers live in states where the city names are unique.
Relation file | Traditional file |
---|---|
Data organized in tables with rows and columns. | Data stored in unstructured formats. |
Supports structured querying with SQL. | Lacks standardized querying abilities. |
Allows for defining relationships between tables. | No inherent support for relationships. |
Offers flexibility in data storage and retrieval. | Limited flexibility in data organisation. |
Examples : MySQL, PostgreSQL | Examples : Text files, CSV files, Excel spreadsheets |
A database is an organized collection of interrelated data that serves many applications, while a table is a collection of logically related records. In other words, it is a named collection of data items that represent a complete unit of information within a database.
A Database Management System (DBMS) is a general-purpose software system that facilitates the process of defining, constructing and manipulating databases for various applications.
Examples of DBMS are MS Access, MySQL.
A primary key is a set of one or more attributes/fields which uniquely identifies a tuple/row in a table.
Its function in a table is to uniquely identify tuples or rows and prevent the entry of duplicate rows, thus ensuring data integrity.
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.
(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;
Match the following clauses with their respective functions.
Column 1 | Column 2 |
---|---|
ALTER | Insert the values in a table |
UPDATE | Restrictions on columns |
DELETE | Table definition |
INSERT INTO | Change the name of a column |
CONSTRAINTS | Update existing information in a table |
DESCRIBE | Delete an existing row from a table |
CREATE | Create a database |
Column 1 | Column 2 |
---|---|
ALTER | Change the name of a column |
UPDATE | Update existing information in a table |
DELETE | Delete an existing row from a table |
INSERT INTO | Insert the values in a table |
CONSTRAINTS | Restrictions on columns |
DESCRIBE | Table definition |
CREATE | Create a database |
Write SQL commands for (a) to (e) 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 |
(a) To show details of all Products 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 sum of the price and stock of the products.
(d) To show products from products table.
(e) To display the details of those products whose Name either ends with 'Y' or 'O'.
(a)
SELECT *
FROM PRODUCTS
WHERE STOCK > 110;
+-------+----------+---------+----------+-------+-------------+----------+
| PCODE | PNAME | COMPANY | PRICE | STOCK | MANUFACTURE | WARRANTY |
+-------+----------+---------+----------+-------+-------------+----------+
| P001 | TV | BPL | 10000.00 | 200 | 2018-01-12 | 3 |
| P002 | TV | SONY | 12000.00 | 150 | 2017-03-23 | 4 |
| P004 | PC | COMPAQ | 38000.00 | 120 | 2019-06-20 | 2 |
| P005 | HANDYCAM | SONY | 18000.00 | 250 | 2017-03-23 | 3 |
+-------+----------+---------+----------+-------+-------------+----------+
(b)
SELECT DISTINCT COMPANY
FROM PRODUCTS
WHERE WARRANTY > 2;
+---------+
| COMPANY |
+---------+
| BPL |
| SONY |
+---------+
(c)
SELECT COMPANY, (PRICE + STOCK) AS STOCK_VALUE
FROM PRODUCTS WHERE COMPANY = 'BPL';
+---------+-------------+
| COMPANY | STOCK_VALUE |
+---------+-------------+
| BPL | 10200 |
+---------+-------------+
(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 * FROM PRODUCTS
WHERE PNAME LIKE '%Y' OR PNAME LIKE '%O';
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. |
Consider the given table and answer the questions.
Table: SchoolBus
Rtno | Area_Covered | Capacity | Noofstudents | Distance | Transporter | Charges |
---|---|---|---|---|---|---|
1 | Vasant Kunj | 100 | 120 | 10 | Shivam Travels | 3500 |
2 | Hauz Khas | 80 | 80 | 10 | Anand Travels | 3000 |
3 | Pitampura | 60 | 55 | 30 | Anand Travels | 4500 |
4 | Rohini | 100 | 90 | 35 | Anand Travels | 5000 |
5 | Yamuna Vihar | 50 | 60 | 20 | Bhalla Travels | 3800 |
6 | Krishna Nagar | 70 | 80 | 30 | Yadav Travels | 4000 |
7 | Vasundhara | 100 | 110 | 20 | Yadav Travels | 3500 |
8 | Paschim Vihar | 40 | 40 | 20 | Speed Travels | 3200 |
9 | Saket | 120 | 120 | 10 | Speed Travels | 3500 |
10 | Janakpuri | 100 | 100 | 20 | Kisan Tours | 3500 |
(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 display the details of school Bus having no. of students less than 50.
(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)
(a)
SELECT * FROM SCHOOLBUS WHERE CAPACITY > 70;
+------+--------------+----------+--------------+----------+----------------+---------+
| RTNO | AREA_COVERED | CAPACITY | NOOFSTUDENTS | DISTANCE | TRANSPORTER | CHARGES |
+------+--------------+----------+--------------+----------+----------------+---------+
| 1 | VASANT KUNJ | 100 | 120 | 10 | SHIVAM TRAVELS | 3500 |
| 2 | HAUZ KHAS | 80 | 80 | 10 | ANAND TRAVELS | 3000 |
| 4 | ROHINI | 100 | 90 | 35 | ANAND TRAVELS | 5000 |
| 7 | VASUNDHARA | 100 | 110 | 20 | YADAV TRAVELS | 3500 |
| 9 | SAKET | 120 | 120 | 10 | SPEED TRAVELS | 3500 |
| 10 | JANAKPURI | 100 | 100 | 20 | KISAN TOURS | 3500 |
+------+--------------+----------+--------------+----------+----------------+---------+
(b)
SELECT AREA_COVERED FROM SCHOOLBUS WHERE DISTANCE > 20 AND CHARGES < 4000;
(c)
SELECT * FROM SCHOOLBUS WHERE NOOFSTUDENTS < 50;
+------+---------------+----------+--------------+----------+---------------+---------+
| RTNO | AREA_COVERED | CAPACITY | NOOFSTUDENTS | DISTANCE | TRANSPORTER | CHARGES |
+------+---------------+----------+--------------+----------+---------------+---------+
| 8 | PASCHIM VIHAR | 40 | 40 | 20 | SPEED TRAVELS | 3200 |
+------+---------------+----------+--------------+----------+---------------+---------+
(d)
SELECT RTNO, AREA_COVERED, (CHARGES/NOOFSTUDENTS) AS AVERAEG_COST
FROM SCHOOLBUS;
+------+---------------+--------------+
| RTNO | AREA_COVERED | AVERAEG_COST |
+------+---------------+--------------+
| 1 | VASANT KUNJ | 29.1667 |
| 2 | HAUZ KHAS | 37.5000 |
| 3 | PITAMPURA | 81.8182 |
| 4 | ROHINI | 55.5556 |
| 5 | YAMUNA VIHAR | 63.3333 |
| 6 | KRISHNA NAGAR | 50.0000 |
| 7 | VASUNDHARA | 31.8182 |
| 8 | PASCHIM VIHAR | 80.0000 |
| 9 | SAKET | 29.1667 |
| 10 | JANAKPURI | 35.0000 |
+------+---------------+--------------+
(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
S NO | ITEM | TYPE | DATEOFSTOCK | PRICE | DISCOUNT |
---|---|---|---|---|---|
1 | WhiteLotus | 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 |
(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;
(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
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 |
(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 for computer as a subject with average of more than 60.
(d) List the names of all students whose name ends with 'a'.
(e) SELECT * FROM GRADUATE WHERE Subject = "Physics";
(f) SELECT DISTINCT RANK FROM GRADUATE;
(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
WHERE NAME LIKE "%a";
+-------+
| NAME |
+-------+
| DIVYA |
| 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 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
Column name | 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 |
SH06 | 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 | FW0S |
03 | Live Life | Delhi | BS01 |
04 | Pretty Woman | Delhi | SH06 |
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 given any discount.
(iv) Write SQL query to display ProductName and price for all products.
(v) 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;
+---------------+-------+
| PRODUCTNAME | PRICE |
+---------------+-------+
| BATH SOAP | 40 |
| FACE WASH | 45 |
| FACE WASH | 55 |
| SHAMPOO | 120 |
| TALCUM POWDER | 95 |
+---------------+-------+
(v) 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.
Write SQL queries based on the table given below:
Table: HOSPITAL
S NO | Name | Age | Department | Datofadm | 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 | Ankita | 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 |
(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, Age for only female patients.
(i)
SELECT NAME
FROM HOSPITAL
WHERE DATEOFADM > '1998-01-15';
+--------+
| NAME |
+--------+
| Arpit |
| Kareem |
| Ketaki |
| Ankit |
| Zareen |
| Shilpa |
+--------+
(ii)
SELECT NAME
FROM HOSPITAL
WHERE SEX = 'F' AND DEPARTMENT = 'ENT';
+---------+
| NAME |
+---------+
| Zareena |
| Ketaki |
+---------+
(iii)
SELECT NAME, DATEOFADM 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 |
| Ankit | 800 | 29 |
| Zareen | 300 | 45 |
| Shilpa | 400 | 23 |
+---------+---------+-----+
False
Reason — The correct syntax for inserting a NULL value is : INSERT INTO TABLE_NAME VALUES (NULL, NULL, ...);
or INSERT INTO TABLE_NAME (COLUMN_NAMES) VALUES (NULL);
.
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.