Simple Queries in SQL

Maximum how many characters can be stored in a

1. text literal
2. numeric literal ?

Answer

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.

What is a datatype ? Name some data types available in MySQL.

Answer

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

What are fixed length fields ? What are variable length fields ?

Answer

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.

Compare Char and Varchar datatypes.

Answer

What is null value in MySQL database ? Can you use nulls in arithmetic expressions ?

Answer

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.

Which keyword eliminates the redundant data from a query result ?

Answer

DISTINCT keyword eliminates the redundant data from a query result.

Which keyword retains duplicate output rows in a query result ?

Answer

ALL keyword retains duplicate output rows in a query result.

How would you display system date as the result of a query ?

Answer

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

How would you calculate 13 * 15 in SQL ?

Answer

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

mysql> SELECT 13 * 15;

Which function is used to substitute NULL values in a query result ?

Answer

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

Which operator concatenates two strings in a query result ?

Answer

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

Which comparison operator is used for comparing ?

1. patterns
2. character values
3. null values
4. ranges
5. list of values.

Answer

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

Which of the following attributes can be considered as a choice for primary key ?

1. Name
2. Street
3. Roll No
4. Subject

Answer

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.

What is the full form of SQL ?

1. Structured Query Language
2. Structured Query List
3. Simple Query Language
4. None of these

Answer

Structured Query Language

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

What is the full form of DDL ?

1. Dynamic Data Language
2. Detailed Data Language
3. Data Definition Language
4. Data Derivation Language

Answer

Data Definition Language

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

What does DML stand for ?

1. Different Mode Level
2. Data Model Language
3. Data Mode Lane
4. Data Manipulation language

Answer

Data Manipulation language

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

Which is the subset of SQL commands used to manipulate database structures, including tables ?

1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Both (1) and (2)
4. None of these

Answer

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.

Which of the following sublanguages of SQL is used to define the structure of the relation, deleting relations and relating schemas?

1. DML (Data Manipulation Language)
2. DDL (Data Definition Language)
3. Query
4. Relational Schema

Answer

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.

Which of the following sublanguages of SQL is used to query information from the database and to insert tuples into, delete tuples from, and modify tuples in the database ?

1. DML (Data Manipulation Language)
2. DDL (Data Definition Language)
3. Query
4. Relational Schema

Answer

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.

Consider following SQL statement. What type of statement is this?

SELECT * FROM employee

1. DML
2. DDL
3. DCL
4. Integrity constraint

Answer

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.

Which of the following keywords will you use in the following query to display the unique values of the column dept_name ?

SELECT ............... dept_name FROM Company;

1. All
2. From
3. Distinct
4. Name

Answer

Distinct

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

Which of the following keywords will you use in the following query to display all the values of the column dept_name ?

SELECT ............... dept_name FROM Company;

1. All
2. From
3. Distinct
4. Name

Answer

All

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

The ............... clause of SELECT query allows us to select only those rows in the result that satisfy a specified condition.

1. Where
2. from
3. having
4. like

Answer

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.

............... clause of the following query must be added with keyword ............... to display the fields given in the select list as per a given condition.

SELECT ID, name, dept name, salary * 1.1
WHERE instructor = 1005 ;

1. where, having
2. select, from
3. where, from
4. where, select

Answer

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 ;

Which of the following queries contains an error?

1. Select * from emp where empid = 10003;
2. Select empid from emp where empid = 10006;
3. Select empid from emp;
4. Select empid where empid = 1009 and lastname = 'GUPTA';

Answer

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

Consider the following table namely Employee :

Which of the names will not be displayed by the below given query ?

SELECT name FROM employee WHERE employee_id > 1009 ;

1. Misha, Khushi
2. Khushi, Japneet
3. Japneet
4. Misha, Japneet

Answer

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.

Which operator performs pattern matching ?

1. BETWEEN operator
2. LIKE operator
3. EXISTS operator
4. None of these

Answer

LIKE operator

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

Consider the following query

SELECT name FROM class WHERE subject LIKE ' ............... Computer Science' ;

Which one of the following has to be added into the blank space to select the subject which has Computer Science as its ending string ?

1. $
2. _
3. ||
4. %

Answer

%

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

Which operator tests a column for the absence of data (i.e., NULL value) ?

1. EXISTS operator
2. NOT operator
3. IS operator
4. None of these

Answer

IS operator

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

The pattern '_ _ _' matches any string of ............... three characters. '_ _ _%' matches any string of ............... three characters.

1. Atleast, Exactly
2. Exactly, Atleast
3. Atleast, All
4. All, Exactly

Answer

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.

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

1. Descending
2. Any
3. Same
4. Ascending

Answer

Ascending

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

Consider the following query

SELECT * FROM employee ORDER BY salary ..............., name ...............;

To display the salary from greater to smaller and name in alphabetical order which of the following options should be used ?

1. Ascending, Descending
2. Asc, Desc
3. Desc, Asc
4. Descending, Ascending

Answer

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.

SQL stands for Structured Query Language.

The SQL keyword FROM is used to specify the table(s) that contains the data to be retrieved.

To remove duplicate rows from the result of a query, specify the SQL qualifier DISTINCT in select list.

To obtain all columns, use a(n) asterisk(*) instead of listing all the column names in the select list.

The SQL WHERE clause contains the condition that specifies which rows are to be selected.

To sort the rows of the result table, the ORDER BY clause is specified.

Columns can be sorted in descending sequence by using the SQL keyword DESC/descending.

When two conditions must both be true for the rows to be selected, the conditions are separated by the SQL keyword AND.

The SQL keyword LIKE is used in SQL expressions to select based on patterns.

By default, ORDER BY clause lists the records in ascending order.

The condition in a WHERE clause in a SELECT query can refer to only one value.

Answer

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

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.

Answer

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

The rows of the result relation produced by a SELECT statement can be sorted, but only by one column.

Answer

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.

SQL is a programming language.

Answer

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.

SELECT DISTINCT is used if a user wishes to see duplicate columns in a query.

Answer

False

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

The qualifier DISTINCT must be used in an SQL statement when we want to eliminate duplicate rows.

Answer

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.

DISTINCT and its counterpart, ALL, can be used more than once in a SELECT statement.

Answer

False

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

DISTINCT and its counterpart, ALL, can be used together on single field in a SELECT statement.

Answer

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.

ORDER BY can be combined with the SELECT statements.

Answer

True

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

Data manipulation language (DML) commands are used to define a database, including creating, altering, and dropping tables and establishing constraints.

Answer

False

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

The keyword LIKE can be used in a WHERE clause to refer to a range of values.

Answer

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.

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

Answer

True

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

Assertion. There is a difference between a field being empty or storing NULL value in a field.

Reason. The NULL value is a legal way of signifying that no value exists in the field.

Answer

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

Assertion. The ALL and DISTINCT clauses of a SELECT query are related.

Reason. The ALL clause is the opposite of the DISTINCT clause of a SELECT Query.

Answer

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

Assertion. The WHERE and HAVING clauses are used for the same thing in a SELECT query.

Reason. Both WHERE and HAVING clauses are used to specify conditions at different levels.

Answer

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

Assertion. Both WHERE and HAVING clauses are used to specify conditions.

Reason. The WHERE and HAVING clauses are interchangeable.

Answer

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

Assertion. DDL and DML are not the same.

Reason. DDL and DML are two subcategories of SQL where DDL creates the objects and DML manipulates the data.

Answer

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

Assertion. DDL and DML both are part of SQL.

Reason. Both DDL and DML are interchangeable.

Answer

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

Assertion. Both BETWEEN and IN operators can choose from a list of values.

Reason. The value ranges and a list of values are interpreted in the same way in SQL.

Answer

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

Write a query to display EName and Sal of employees whose salary is greater than or equal to 2200 from table Empl.

Answer

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

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

Write a query to display details of employees who are not getting commission from table Empl.

Answer

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

Write a query to display employee name and salary of those employee who don't have their salary in the range of 2500 to 4000.

Answer

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

Write a query to display the name, job title and salary of employee who do not have manager.

Answer

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

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

Write a query to display the name of employee whose name contains 'A' as third alphabet.

Answer

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.

Write a query to display the name of employee whose name contains 'T' as the last alphabet.

Answer

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

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

Write a query to display the name of employee whose name contains 'M' as first alphabet 'L' as third alphabet.

Answer

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.

Write a query on the customers table whose output will exclude all customers with a rating <= 100, unless they are located in Shimla.

Answer

SELECT *
FROM customers
WHERE rating > 100 OR city = 'Shimla' ;

Write a query that selects all orders (Order table) except those with zeros or NULLs in the amt field.

Answer

SELECT *
FROM order
WHERE amt IS NOT NULL AND amt <> 0 ;

Write SQL commands for the following on the basis of given table STUDENT :

Table : STUDENT

1. Display the names of the students who are getting a grade 'C' in either GAME or SUPW.
2. Display the different games offered in the school.
3. Display the SUPW taken up by the students, whose name starts with 'A'.

Answer

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

Write SQL commands for the following on the basis of given table SPORTS :

Table : SPORTS

1. Display the names of the students who have grade 'C' in either Game1 or Game2 or both.
2. Display the names of the students who have same game for both Game1 and Game2.
3. Display the games taken up by the students, whose name starts with 'A'.

Answer

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

Write SQL commands for the following on the basis of given table CLUB :

Table : CLUB

1. To show all information about the swimming coaches in the club.
2. To list names of all coaches with their date of appointment (DATOFAPP) in descending order.
3. To display a report, showing coachname, pay, age and bonus (15% of pay) for all the coaches.

Answer

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

Write SQL commands for the following on the basis of given table STUDENT1 :

Table : STUDENT1

1. Select all the Nonmedical stream students from STUDENT1.
2. List the names of those students who are in class 12 sorted by Stipend.
3. List all students sorted by AvgMark in descending order.
4. Display a report, listing Name, Stipend, Stream and amount of stipend received in a year assuming that the Stipend is paid every month.

Answer

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

Consider the table Student1 of Q. 13. Give the output of following SQL statement :

1. SELECT TRUNCATE(AvgMark) FROM Student1 WHERE AvgMark < 75 ;
2. SELECT ROUND(AvgMark) FROM Student1 WHERE Grade = 'B' ;
3. SELECT CONCAT(Name, Stream) FROM Student1 WHERE Class = '12A' ;
4. SELECT RIGHT(Stream, 2) FROM Student1 ;

Answer

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

Given the following table :

Table : STUDENT

Give the output of following SQL statements :

1. SELECT MIN(AvgMark) FROM STUDENT WHERE AvgMark < 75 ;
2. SELECT SUM(Stipend) FROM STUDENT WHERE Grade = 'B' ;
3. SELECT AVG(Stipend) FROM STUDENT WHERE Class = '12A' ;
4. SELECT COUNT(DISTINCT) FROM STUDENT ;

Answer

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.

Write SQL commands for the following on the basis of given table MOV :

Table : MOV

1. Display a list of all movies with Price over 20 and sorted by Price.
2. Display all the movies sorted by QTY in decreasing order.
3. Display a report listing a movie number, current value and replacement value for each movie in the above table. Calculate the replacement value for all movies as : QTY * Price * 1.15.

Answer

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

Write SQL commands for the following on the basis of given table Teacher :

Table : Teacher

1. To show all information about the teacher of history department.
2. To list the names of female teachers who are in Hindi department.
3. To list names of all teachers with their date of joining in ascending order.

Answer

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