Review of Database Concepts & SQL

A Database is an organized collection of records.

DBMS stands for Database Management System.

Data Redundancy means duplication of data.

Tuples are known as rows and Attributes are known as columns in RDBMS.

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

MySQL provides a dummy table called Dual.

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

The Describe or Desc statement is used to view the structure of a table.

The Where clause is used to select specific rows.

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

Select * statement displays all Columns of a table.

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

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

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

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

In relational data model a table is called relation.

Answer

True

Reason — In the relational data model, the data is organized into tables (i.e., rows and columns). These tables are called relation.

A row in a table will not represent a relationship among a set of values.

Answer

False

Reason — In the relational data model, a row in a table represents a relationship among a set of values.

Data Item is the smallest unit of named data.

Answer

True

Reason — In the relational data model, a data item is the smallest unit of named data. It is an atomic value that is the basic building block of a relational database.

Data Item represents multi-type of information in a field.

Answer

False

Reason — A data item, in the relational data model, represents a single value or a single type of information in a field, not multi-type.

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 — The 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_NAMES) 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 structure of a table.

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

Answer

True

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

............... is a named collection of data items which represents a complete unit of information.

1. Field
2. Record
3. Table
4. Database

Answer

Record

Reason — Record is a named collection of data items which represents a complete unit of information.

A ............... is a named collection of all occurrences of a given type of logical record.

1. Field
2. Record
3. Relation
4. Database

Answer

Relation

Reason — A relation (table) is a named collection of all occurrences of a given type of logical record.

The number of attributes in a relation determines the ............... of a relation.

1. Degree
2. Tuples
3. Attributes
4. Cardinality

Answer

Degree

Reason — The total number of columns or attributes in a relation is known as degree of a relation.

The rows of the relations are generally referred to as ............... .

1. Degree
2. Tuples
3. Attributes
4. Cardinality

Answer

Tuples

Reason — The rows of the relations are referred to as tuples.

What is a database?

1. Organized collection of information that cannot be accessed, updated and managed
2. Collection of data or information without organizing
3. Organized collection of data or information that can be accessed, updated and managed
4. Organized collection of data that cannot be updated

Answer

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.

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

1. Primary Key
2. Cardinality
3. Attributes
4. Foreign Key

Answer

Primary Key

Reason — A primary key is a set of one or more attributes/fields which uniquely identifies a tuple/row in a table.

A candidate key that is not the primary key is called a/an ............... .

1. Primary Key
2. Alternate Key
3. Candidate Key
4. Foreign Key

Answer

Alternate Key

Reason — A candidate key that is not selected as the primary key is called an alternate key.

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

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

Answer

DDL

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

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

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

Answer

DML

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

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

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

Answer

Comment

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

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

1. Keywords
2. Literals
3. Variables
4. Tables

Answer

Keywords

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

Which of the following are DDL commands?

(A) Delete

(B) Create

(C) Update

(D) Alter

(E) Drop

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

Answer

(B), (D) and (E)

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

Identify the correct statement(s):

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

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

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

Answer

(S1): Correct, (S2) : Incorrect

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

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

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

Answer

Distinct

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

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

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

Answer

;

Reason — The SQL statements always ends with semicolon (;) to indicate the end of the statement.

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

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

Answer

Desc

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

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

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

Answer

Alter

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

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

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

Answer

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

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

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

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

Answer

All data from the table

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

Consider the attributes ( 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.

2.

3.

4.

Answer

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

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

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

Answer

4, 20

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

Assertion (A): 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.

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

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

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

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

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

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

Answer

A is false but R is true.

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

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

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

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

Answer

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

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

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

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

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

Answer

A is true but R is false.

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

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

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

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

Answer

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

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

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

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

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

Answer

A is true but R is false.

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

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

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

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

Answer

A is true but R is false.

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

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.

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.

Answer

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.

Answer

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:

1. It should have a column TeamID for storing an integer value between 1 and 9, which refers to unique identification of a team.
2. Each TeamID should have its associated name (TeamName), which should be a string of length not less than 10 characters.
3. Give the statement to make TeamID the primary key.

(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.

Answer

(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 data?

Answer

Data is the smallest unit of file organization which is represented in the form of a bit that may either be 0 or 1.

What do you mean by information?

Answer

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.

What is the difference between data and information?

Answer

What is database and database system? What are the elements of database system?

Answer

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.

Why do we need a database?

Answer

We need a database because it provides a structured and organized way to store, manage, and retrieve large amounts of data efficiently and effectively.

What is database management system? Why do we need a DBMS?

Answer

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.

Explain the difference between database and file.

Answer

Explain the difference between data and file.

Answer

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.

Answer

(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.

Define Relation.

Answer

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

Define Domain.

Answer

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

Define Tuple.

Answer

The rows of tables (relations) are called tuples.

Define Attribute.

Answer

The columns of tables (relations) are called attributes.

Define Degree.

Answer

The number of attributes in a relation is called degree of a relation.

Define Cardinality.

Answer

The number of rows in a relation is known as cardinality of the relation.

Summarize the major differences between a relation and a traditional file.

Answer

What is the relationship between a database and a table?

Answer

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.

What is DBMS? Write names of any two DBMSs.

Answer

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.

How is data organized in a table?

Answer

Data in a table is organized into rows and columns. Each row represents a record or tuple, while each column represents an attribute or field of the data.

What is a primary key? What is its function in a table?

Answer

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.

Answer

(a)

SELECT * FROM PRODUCT
WHERE prod_id > 100;

(b)

SELECT * FROM PRODUCT
WHERE prod_name = 'Almirah';

(c)

SELECT * FROM PRODUCT
WHERE price BETWEEN 200 AND 500; 

(d)

SELECT prod_name
FROM PRODUCT
WHERE quantity IS NULL;

(e)

SELECT * FROM PRODUCT;

Define Database.

Answer

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

Define Data inconsistency.

Answer

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

Define Primary Key.

Answer

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

Define Candidate Key.

Answer

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

Define Alternate Key.

Answer

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

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.

Match the following clauses with their respective functions.

Answer

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

Table: PRODUCTS

(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'.

Answer

(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';

What are DDL and DML?

Answer

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

Differentiate between primary key and candidate key in a relation.

Answer

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

Answer

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

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

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

Answer

Consider the given table and answer the questions.

Table: SchoolBus

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

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

(c) To 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)

Answer

(a)

SELECT * FROM SCHOOLBUS WHERE CAPACITY > 70;

+------+--------------+----------+--------------+----------+----------------+---------+
| RTNO | AREA_COVERED | CAPACITY | NOOFSTUDENTS | DISTANCE | TRANSPORTER    | CHARGES |
+------+--------------+----------+--------------+----------+----------------+---------+
|    1 | VASANT KUNJ  |      100 |          120 |       10 | SHIVAM TRAVELS |    3500 |
|    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

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

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

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

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

(e) Select Distinct Type from Furniture;

Answer

(a)

SELECT * FROM FURNITURE
WHERE PRICE > 10000;

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

(b)

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

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

(c)

DELETE FROM FURNITURE WHERE DISCOUNT = 30;

(d)

SELECT PRICE
FROM FURNITURE
WHERE TYPE = 'BabyCot';

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

(e)

SELECT DISTINCT Type FROM Furniture;

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

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

Table: GRADUATE

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

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

(c) Display the names of those students who have opted 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;

Answer

(a)

SELECT NAME FROM GRADUATE 
WHERE `RANK` = 1;

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

(b)

SELECT NAME
FROM GRADUATE
WHERE AVERAGE > 65;

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

(c)

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

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

(d)

SELECT NAME FROM GRADUATE 
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 |
+------+

What is the difference between Candidate key and Alternate key?

Answer

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

Answer

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

For the given table, do as directed:

Table: STUDENT

(i) Write SQL query to create the table.

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

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

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

Answer

(i)

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

(ii)

ALTER TABLE STUDENT MODIFY SNAME VARCHAR(30);

(iii)

ALTER TABLE STUDENT DROP HOBBY;

(iv)

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

Write SQL queries based on the following tables:

Table: PRODUCT

Table: CLIENT

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

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

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

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

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

Answer

(i)

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

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

(ii)

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

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

(iii)

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

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

(iv)

SELECT ProductName, Price FROM PRODUCT;

+---------------+-------+
| PRODUCTNAME   | PRICE |
+---------------+-------+
| BATH SOAP     |    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

(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.

Answer

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