Simple Queries in SQL

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
There is a difference between a field being empty or storing NULL value in a field. An empty field means that the field contains a value, but that value is an empty string or a space, depending on the data type. In contrast, if a column in a row has no value, then column is said to contain a NULL. Hence, the NULL value is a legal way of signifying that no value exists in the field.

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
The ALL and DISTINCT clauses in a SELECT query are related in that they both affect how duplicate rows are handled in the result. The DISTINCT keyword in SQL is used to eliminate duplicate rows from the results of a query. The ALL keyword in SQL is used to retain the duplicate rows from the results of a query. Therefore, the ALL clause is the opposite of the DISTINCT clause of a SELECT Query.

(d)

Assertion is false but Reason is true.

Explanation
The WHERE and HAVING clauses are not used for the same thing in a SELECT query. While both WHERE and HAVING clauses are used to specify conditions in a SELECT query, they operate at different levels. The WHERE clause filters rows based on conditions applied to individual rows before grouping, while the HAVING clause filters groups based on conditions applied to the result of aggregation functions after grouping.

(b)

Both Assertion and Reason are true but Reason is not the correct explanation of Assertion.

Explanation
Both WHERE and HAVING clauses are used to specify conditions in a SELECT query, they operate at different levels. The WHERE clause filters rows based on conditions applied to individual rows before grouping, while the HAVING clause filters groups based on conditions applied to the result of aggregation functions after grouping. Hence, they are not interchangeable.

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
DDL and DML commands are two different commands of SQL. Data definition language (DDL) commands in SQL are used to define a database, including creating, altering, and dropping tables whereas Data manipulation language (DML) commands are used to retrieve, insert, update, and delete data in a database.

(c)

Assertion is true but Reason is false.

Explanation
DDL and DML are two subcategories of SQL. Data definition language (DDL) commands in SQL are used to define a database, including creating, altering, and dropping tables whereas Data manipulation language (DML) statements are used to retrieve, insert, update, and delete data in a database. Hence, DDL and DML are two different commands and are not interchangeable.

(c)

Assertion is true but Reason is false.

Explanation
Both BETWEEN and IN operators can select values from a list. The BETWEEN operator defines a range of values into which column values must fall to make the condition true. This range includes both lower and upper values. In contrast, the IN operator is used to specify a list of values. It selects values that match any value in the given list of values. Therefore, while both operators involve selecting values from a list, they operate differently in SQL, distinguishing between value ranges and specific value lists.

SELECT ENAME, SAL  
FROM empl   
WHERE SAL >= 2200;

+-----------+------+
| ENAME     | SAL  |
+-----------+------+
| MAHADEVAN | 2985 |
| BINA      | 2850 |
| AMIR      | 5000 |
| SHIAVNSH  | 2450 |
| SCOTT     | 3000 |
| FAKIR     | 3000 |
+-----------+------+

SELECT *
FROM empl
WHERE COMM IS NULL OR COMM = 0;

+-------+-----------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME     | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
+-------+-----------+-----------+------+------------+------+------+--------+
|  8369 | SMITH     | CLERK     | 8902 | 1990-12-18 |  800 | NULL |     20 |
|  8566 | MAHADEVAN | MANAGER   | 8839 | 1991-04-02 | 2985 | NULL |     20 |
|  8698 | BINA      | MANAGER   | 8839 | 1991-05-01 | 2850 | NULL |     30 |
|  8839 | AMIR      | PRESIDENT | NULL | 1991-11-18 | 5000 | NULL |     10 |
|  8844 | KULDEEP   | SALESMAN  | 8698 | 1991-09-08 | 1500 |    0 |     30 |
|  8882 | SHIAVNSH  | MANAGER   | 8839 | 1991-06-09 | 2450 | NULL |     10 |
|  8886 | ANOOP     | CLERK     | 8888 | 1993-01-12 | 1100 | NULL |     20 |
|  8888 | SCOTT     | ANALYST   | 8566 | 1992-12-09 | 3000 | NULL |     20 |
|  8900 | JATIN     | CLERK     | 8698 | 1991-12-03 |  950 | NULL |     30 |
|  8902 | FAKIR     | ANALYST   | 8566 | 1991-12-03 | 3000 | NULL |     20 |
|  8934 | MITA      | CLERK     | 8882 | 1992-01-23 | 1300 | NULL |     10 |
+-------+-----------+-----------+------+------------+------+------+--------+

SELECT ENAME, SAL
FROM empl
WHERE SAL NOT BETWEEN 2500 AND 4000;

+----------+------+
| ENAME    | SAL  |
+----------+------+
| SMITH    |  800 |
| ANYA     | 1600 |
| SETH     | 1250 |
| MOMIN    | 1250 |
| AMIR     | 5000 |
| KULDEEP  | 1500 |
| SHIAVNSH | 2450 |
| ANOOP    | 1100 |
| JATIN    |  950 |
| MITA     | 1300 |
+----------+------+

SELECT ENAME, JOB, SAL
FROM empl
WHERE MGR IS NULL ;

+-------+-----------+------+
| ENAME | JOB       | SAL  |
+-------+-----------+------+
| AMIR  | PRESIDENT | 5000 |
+-------+-----------+------+

SELECT ENAME
FROM empl
WHERE ENAME LIKE '__A%' ;

There are no employees whose name contains 'A' as the third alphabet in the empl table. Therefore, the output will be empty.

SELECT ENAME
FROM empl
WHERE ENAME LIKE '%T' ;

+-------+
| ENAME |
+-------+
| SCOTT |
+-------+

SELECT ENAME
FROM empl
WHERE ENAME LIKE 'M_L%' ;

There are no employees whose name contains 'M' as first alphabet and 'L' as third alphabet in the empl table. Therefore, the output will be empty.

1.

SELECT Name
FROM STUDENT
WHERE Grade1 = 'C' OR Grade2 = 'C' ;

+-------+
| Name  |
+-------+
| Sujit |
| Veena |
| Arpit |
+-------+

2.

SELECT DISTINCT GAME
FROM STUDENT ;

+-------------+
| GAME        |
+-------------+
| Cricket     |
| Tennis      |
| Swimming    |
| Basket Ball |
+-------------+

3.

SELECT SUPW
FROM STUDENT
WHERE Name LIKE 'A%' ;

+------------+
| SUPW       |
+------------+
| Literature |
| Gardening  |
+------------+

1.

SELECT Name
FROM SPORTS
WHERE Grade1 = 'C' OR Grade2 = 'C' ;

+-------+
| Name  |
+-------+
| Sujit |
| Venna |
| Arpit |
+-------+

2.

SELECT Name
FROM SPORTS
WHERE Game1 = Game2 ;

+-------+
| Name  |
+-------+
| Venna |
+-------+

3.

SELECT Game1, Game2
FROM SPORTS
WHERE Name LIKE 'A%' ;

+------------+-----------+
| Game1      | Game2     |
+------------+-----------+
| Basketball | Cricket   |
| Cricket    | Athletics |
+------------+-----------+

1.

SELECT *
FROM CLUB 
WHERE SPORTS = 'SWIMMING' ;

+----------+-----------+-----+----------+-----+-----+------------+
| COACH_ID | COACHNAME | AGE | SPORTS   | PAY | SEX | DATOFAPP   |
+----------+-----------+-----+----------+-----+-----+------------+
|        5 | ZUBIN     |  36 | SWIMMING | 750 | M   | 1998-01-12 |
|        6 | KETAKI    |  36 | SWIMMING | 800 | F   | 1998-02-24 |
|        9 | KUSH      |  41 | SWIMMING | 900 | M   | 1998-01-13 |
+----------+-----------+-----+----------+-----+-----+------------+

2.

SELECT COACHNAME, DATOFAPP
FROM CLUB
ORDER BY DATOFAPP DESC ;

+-----------+------------+
| COACHNAME | DATOFAPP   |
+-----------+------------+
| KETAKI    | 1998-02-24 |
| ZAREEN    | 1998-02-22 |
| ANKITA    | 1998-02-20 |
| KARAN     | 1998-02-19 |
| SHAILYA   | 1998-02-19 |
| RAVINA    | 1998-01-20 |
| KUSH      | 1998-01-13 |
| ZUBIN     | 1998-01-12 |
| TARUN     | 1998-01-01 |
| KUKREJA   | 1996-03-27 |
+-----------+------------+

3.

SELECT COACHNAME, PAY, AGE, (PAY * 0.15) AS BONUS  
FROM CLUB ;

+-----------+------+-----+--------+
| COACHNAME | PAY  | AGE | BONUS  |
+-----------+------+-----+--------+
| KUKREJA   | 1000 |  35 | 150.00 |
| RAVINA    | 1200 |  34 | 180.00 |
| KARAN     | 2000 |  34 | 300.00 |
| TARUN     | 1500 |  33 | 225.00 |
| ZUBIN     |  750 |  36 | 112.50 |
| KETAKI    |  800 |  36 | 120.00 |
| ANKITA    | 2200 |  39 | 330.00 |
| ZAREEN    | 1100 |  37 | 165.00 |
| KUSH      |  900 |  41 | 135.00 |
| SHAILYA   | 1700 |  37 | 255.00 |
+-----------+------+-----+--------+

1.

SELECT *
FROM STUDENT1
WHERE Stream = 'Nonmedical' ;

+-----+--------+---------+------------+---------+-------+-------+
| No. | Name   | Stipend | Stream     | AvgMark | Grade | Class |
+-----+--------+---------+------------+---------+-------+-------+
|   5 | Sabina |     500 | Nonmedical | 90.6    | A     | 11A   |
|   8 | Rubina |     450 | Nonmedical | 88.5    | A     | 12A   |
|   9 | Vikas  |     500 | Nonmedical | 92.0    | A     | 12A   |
+-----+--------+---------+------------+---------+-------+-------+

2.

SELECT Name 
FROM STUDENT1
WHERE Class LIKE '12%'
ORDER BY Stipend ;

+--------+
| Name   |
+--------+
| Divya  |
| Mohan  |
| Arun   |
| Karan  |
| John   |
| Rubina |
| Vikas  |
+--------+

3.

SELECT *
FROM STUDENT1
ORDER BY AvgMark DESC ;

+-----+---------+---------+------------+---------+-------+-------+
| No. | Name    | Stipend | Stream     | AvgMark | Grade | Class |
+-----+---------+---------+------------+---------+-------+-------+
|   9 | Vikas   |     500 | Nonmedical | 92.0    | A     | 12A   |
|   5 | Sabina  |     500 | Nonmedical | 90.6    | A     | 11A   |
|   2 | Divakar |     450 | Commerce   | 89.2    | A     | 11C   |
|   8 | Rubina  |     450 | Nonmedical | 88.5    | A     | 12A   |
|   1 | Karan   |     400 | Medical    | 78.5    | B     | 12B   |
|   6 | John    |     400 | Medical    | 75.4    | B     | 12B   |
|   4 | Arun    |     350 | Humanities | 73.1    | B     | 12C   |
|   3 | Divya   |     300 | Commerce   | 68.6    | C     | 12C   |
|  10 | Mohan   |     300 | Commerce   | 67.5    | C     | 12C   |
|   7 | Robert  |     250 | Humanities | 64.4    | C     | 11A   |
+-----+---------+---------+------------+---------+-------+-------+

4.

SELECT Name, Stipend, Stream, (Stipend * 12) AS Yearly_Stipend 
FROM STUDENT1 ;

+---------+---------+------------+----------------+
| Name    | Stipend | Stream     | Yearly_Stipend |
+---------+---------+------------+----------------+
| Karan   |     400 | Medical    |           4800 |
| Divakar |     450 | Commerce   |           5400 |
| Divya   |     300 | Commerce   |           3600 |
| Arun    |     350 | Humanities |           4200 |
| Sabina  |     500 | Nonmedical |           6000 |
| John    |     400 | Medical    |           4800 |
| Robert  |     250 | Humanities |           3000 |
| Rubina  |     450 | Nonmedical |           5400 |
| Vikas   |     500 | Nonmedical |           6000 |
| Mohan   |     300 | Commerce   |           3600 |
+---------+---------+------------+----------------+

1. It will return error because no argument is passed as decimal places to truncate. Syntax of truncate function is TRUNCATE(number, decimals).

2.

+----------------+
| ROUND(AvgMark) |
+----------------+
|             78 |
|             73 |
|             75 |
+----------------+

3.

+----------------------+
| CONCAT(Name, Stream) |
+----------------------+
| RubinaNonmedical     |
| VikasNonmedical      |
+----------------------+

4.

+------------------+
| RIGHT(Stream, 2) |
+------------------+
| al               |
| ce               |
| ce               |
| es               |
| al               |
| al               |
| es               |
| al               |
| al               |
| ce               |
+------------------+

1.

+--------------+
| MIN(AvgMark) |
+--------------+
| 64.4         |
+--------------+

2.

+--------------+
| SUM(Stipend) |
+--------------+
|         1150 |
+--------------+

3.

+--------------+
| AVG(Stipend) |
+--------------+
|          475 |
+--------------+

4. It will give an error because the COUNT function requires an argument specifying what to count. Additionally, the DISTINCT keyword is followed by a column name to count the distinct values of that column.

1.

SELECT Title
FROM MOV
WHERE Price > 20
ORDER BY Price ;

+--------------------+
| Title              |
+--------------------+
| Splash             |
| Tootsie            |
| Cocoon             |
| Gone with the Wind |
| Risky Business     |
| Top Gun            |
| 101 Dalmatians     |
| Friday the 13th    |
| Crocodile Dundee   |
+--------------------+

2.

SELECT Title
FROM MOV
ORDER BY Qty DESC ;

+--------------------+
| Title              |
+--------------------+
| Top Gun            |
| Gone with the Wind |
| Splash             |
| Independence Day   |
| 101 Dalmatians     |
| Friday the 13th    |
| Risky Business     |
| Cocoon             |
| Crocodile Dundee   |
| Tootsie            |
+--------------------+

3.

SELECT No AS Movie_Number , Price AS Current_Value, (Qty * Price * 1.15) AS Replacement_Value
FROM MOV ;

+--------------+---------------+--------------------+
| Movie_Number | Current_Value | Replacement_Value  |
+--------------+---------------+--------------------+
|            1 |         39.95 | 183.77000350952147 |
|            2 |         69.95 |   160.884992980957 |
|            3 |         49.95 | 402.09750614166256 |
|            4 |         29.95 |  103.3275026321411 |
|            5 |         19.95 |   68.8275026321411 |
|            6 |         44.95 | 103.38500175476074 |
|            7 |         31.95 |  73.48500175476073 |
|            8 |         69.95 |   160.884992980957 |
|            9 |         59.95 |  206.8275026321411 |
|           10 |         29.95 |  34.44250087738037 |
+--------------+---------------+--------------------+

1.

SELECT *
FROM Teacher
WHERE Department = 'History' ;

+----+----------+-----+------------+--------+-----+------------+
| No | Name     | Age | Department | Salary | Sex | Dateofjoin |
+----+----------+-----+------------+--------+-----+------------+
|  2 | Sharmila |  31 | History    |  20000 | F   | 1998-03-24 |
|  4 | Sangeeta |  35 | History    |  40000 | F   | 1999-07-01 |
|  6 | Shyam    |  50 | History    |  30000 | M   | 1998-06-27 |
+----+----------+-----+------------+--------+-----+------------+

2.

SELECT Name
FROM Teacher
WHERE Sex = 'F' and Department = 'Hindi' ;

There are no records in the Teacher table where the department is 'Hindi'. Hence, there will be no output.

3.

SELECT Name, Dateofjoin
FROM Teacher
ORDER BY Dateofjoin ;

+----------+------------+
| Name     | Dateofjoin |
+----------+------------+
| Sandeep  | 1996-12-12 |
| Jugal    | 1997-01-10 |
| Shiv Om  | 1997-02-25 |
| Shalakha | 1997-07-31 |
| Rakesh   | 1997-09-05 |
| Sharmila | 1998-03-24 |
| Shyam    | 1998-06-27 |
| Sangeeta | 1999-07-01 |
+----------+------------+

1. A text literal can have maximum length of 4000 bytes in MySQL.
2. A numeric literal can store a maximum of 53 digits of precision.

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

Fixed length fields have fixed lengths i.e., they occupy fixed number of bytes for every data element they store. These number of bytes are determined by maximum number of characters the field can store.

Variable length fields have varied field lengths i.e., field length is determined separately for every data element inside the field. The number of characters in the data element become its field length.

If a column in a row has no value, then column is said to be null, or to contain a null. Yes, we can use nulls in arithmetic expressions. Any arithmetic expression containing a null, always evaluates to null.

DISTINCT keyword eliminates the redundant data from a query result.

ALL keyword retains duplicate output rows in a query result.

The current system date can be obtained, using function CURDATE(), as shown below :
mysql>SELECT CURDATE();.

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

mysql> SELECT 13 * 15;

IFNULL() function is used to substitute NULL values in a query result.

CONCAT() function is used to concatenate two strings in a query result.

1. Patterns — LIKE
2. Character values — = and <>
3. Null values — IS NULL and IS NOT NULL
4. Ranges — BETWEEN
5. List of values — IN

Roll No

Reason — A primary key is a unique identifier for each record in a table, and it must be unique and not null. As the "Roll No" is unique for each student and can uniquely identify each record in the table, it can be considered as a choice for primary key.

Structured Query Language

Reason — The full form of SQL is Structured Query Language.

Data Definition Language

Reason — The full form of DDL is Data Definition Language.

Data Manipulation language

Reason — The full form of DML is Data Manipulation language.

Data Definition Language (DDL)

Reason — Data Definition Language (DDL) commands are used to define and manipulate database structures, including creating, altering, and dropping tables, indexes, views, and other schema objects.

DDL (Data Definition Language)

Reason — In SQL, Data Definition Language (DDL) statements are used to define the structure of the database, including creating, relating, altering, and dropping database objects such as tables, indexes, and views.

DML (Data Manipulation Language)

Reason — In SQL, Data Manipulation Language (DML) statements are used to manipulate data in the database. DML statements are used to query information from the database, as well as to insert, delete, and modify tuples (rows) in the database tables.

DML

Reason — Data Manipulation Language (DML) statements are used to retrieve, insert, update, and delete data in a database. The 'SELECT' statement, in particular, is used to retrieve data from one or more tables.

Distinct

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

All

Reason — The All keyword is used to display all values of the column.

Where

Reason — The WHERE clause in SELECT statement specifies the criteria for selection of rows to be returned. When a WHERE clause is present, the database program goes through the entire table one row at a time and examines each row to determine if the given condition is true. If it is true for a row, that row is displayed in the output.

select, from

Reason — In SQL, the SELECT clause is used to retrieve a subset of rows and columns from one or more tables, while the FROM clause specifies the table from which the data should be retrieved. Therefore, to complete the query, the FROM clause must be added after the SELECT keyword. The corrected query is as follows :

SELECT ID, name, dept name, salary * 1.1
FROM <table_name>
WHERE instructor = 1005 ;

Select empid where empid = 1009 and lastname = 'GUPTA';

Reason — This query lacks the FROM clause. In SQL, the FROM clause is required to specify the table from which we are selecting data. Without it, the query is incomplete and will result in a syntax error. The corrected query is as follows :

Select empid from emp where empid = 1009 and lastname = 'GUPTA';

Misha, Khushi

Reason — The query SELECT name FROM employee WHERE employee_id > 1009; retrieves the names of employees whose employee_id is greater than 1009. Japneet has an employee_id of 1018, which is greater than 1009, so Japneet will be displayed. But the question asks for the names which will not be displayed by the query. Hence, the correct answer will be Misha, Khushi, as they have employee_id ≤ 1009.

LIKE operator

Reason — SQL includes a string-matching operator, LIKE, for comparisons on character strings using patterns.

%

Reason — The % wildcard character in SQL pattern matching, matches any substring, so %Computer Science would match any string ending with 'Computer Science'. Therefore, the correct option to fill in the blank space is %.

IS operator

Reason — The NULL value in a column can be searched in a table using the IS operator.

Exactly, Atleast

Reason — '_ _ _' matches any string of exactly 3 characters. Each dash represents one character, so there must be three characters in total. '_ _ _%' matches any string of at least 3 characters. The first three dashes represent exactly three characters, and the '%' symbol matches any substring. So, it matches any string with three or more characters.

Ascending

Reason — By default, ORDER BY clause lists the results in ascending order.

Desc, Asc
Descending, Ascending

Reason — To display the salary from greater to smaller i.e., in descending order we use DESC or descending keyword and to sort name in alphabetical order i.e., in ascending order we use ASC or ascending keyword.

False

Reason — In SQL, the condition in a WHERE clause can refer to multiple values. We can use logical operators such as AND, OR, and NOT to combine multiple conditions. For example :

SELECT * FROM pet WHERE (species = 'cat' OR species = 'dog') AND sex = 'm';

True

Reason — SQL provides the AS keyword, which can be used to assign meaningful column names to the results of queries using the SQL built-in functions. The syntax is as follows :

SELECT <column name> AS [column alias] [, <column name> AS [column alias]] FROM <table name> ;

False

Reason — In SQL, the ORDER BY clause is used to sort the rows of the result relation produced by a SELECT statement. It allows sorting by one or more columns in ascending or descending order.

False

Reason — SQL, Structured Query Language, is a non-procedural query language. It describes WHAT all data is to be retrieved or inserted or modified or deleted, rather than specifying code describing HOW to perform the entire operation. Hence, it is not a programming language.

False

Reason — SELECT DISTINCT statement is used if a user wishes to eliminate duplicate rows from the results of a query.

True

Reason — The DISTINCT keyword in SQL is used to eliminate duplicate rows from the results of a query. Therefore, when a user wants to ensure that only unique rows are returned, they must use the DISTINCT qualifier in their SQL statement.

False

Reason — In SQL, both DISTINCT and ALL keywords can be specified only once in a given SELECT clause.

False

Reason — In SQL, DISTINCT and ALL cannot be used together on a single field in a SELECT statement. As DISTINCT eliminates duplicate rows from the results, while ALL includes all rows, including duplicates. Therefore, attempting to use them together on the same field would result in a syntax error.

True

Reason — ORDER BY can be combined with the SELECT statements in SQL to sort query results based on one or more columns.

False

Reason — Data definition language (DDL) commands in SQL are used to define a database, including creating, altering, and dropping tables and establishing constraints.

False

Reason — The keyword BETWEEN in SQL can be used in a WHERE clause to refer to a range of values. While the keyword LIKE can be used in a WHERE clause for comparison of character strings using patterns.

True

Reason — The keyword BETWEEN in SQL can be used in a WHERE clause to refer to a range of values.