MySQL SQL Revision Tour

What is SQL ? What are the different categories of SQL commands ?

Answer

SQL (Structured Query Language) is a standard language for accessing and manipulating databases.

The different categories of SQL commands are as follows:

1. Data Definition Language (DDL) Commands
2. Data Manipulation Language (DML) Commands
3. Data Query Language (DQL) command
4. Data Control Language (DCL) commands
5. Transaction Control Language (TCL) Commands

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.

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;

Is NULL value the same as 0 (zero) ? Write the reason for your answer.

Answer

No, a NULL value is not the same as 0 (zero) in SQL. In SQL, NULL represents a missing or unknown value and is a legal empty value. On the other hand, 0 is a specific numeric value representing the number zero.

Write the UPDATE command to increase the commission (Column name : COMM) by 500 of all the Salesmen who have achieved Sales (Column name : SALES) more than 200000. The table's name is COMPANY.

Answer

UPDATE COMPANY
SET COMM = COMM + 500
WHERE SALES > 200000;

While using SQL pattern matching, what is the difference between '_' (underscore) and '%' wildcard symbols ?

Answer

In SQL pattern matching, the underscore ('_') character matches any character, while the percent ('%') wildcard character matches any substring.

Write one similarity and one difference between CHAR and VARCHAR data types.

Answer

One similarity between the CHAR and VARCHAR data types in SQL is that both are used to store character data.

One difference between the CHAR and VARCHAR data types in SQL is that the Char datatype specifies a fixed length character string, while the Varchar datatype specifies a variable length string.

Write SQL statement to display

Today, the date is <current date>

Answer

SELECT CONCAT('Today, the date is ', CURDATE()) AS CURDATE;

Write a command to add a NOT NULL constraint on FEES column of a student table.

Answer

ALTER TABLE student
MODIFY COLUMN FEES INT NOT NULL;

What is a constraint ? Name some constraints that you can apply to enhance database integrity.

Answer

A constraint is a condition or check applicable on a field or set of fields. Some constraints that we can apply to enhance database integrity are:

1. Unique constraint.
2. Primary key constraint.
3. Default constraint.
4. Check constraint.
5. Foreign key constraint.

What is primary key ? What is PRIMARY KEY constraint ?

Answer

A primary key is a unique identifier for each record in a table, and it must be unique and not null. A PRIMARY KEY constraint declares a column or one group of columns as the primary key of the table. This constraint must be applied to columns declared as NOT NULL.

What is NOT NULL constraint ? What is DEFAULT constraint ?

Answer

The NOT NULL constraint is used in SQL to ensure that a column cannot contain NULL (i.e., empty) values.

A DEFAULT constraint is used in SQL to specify a default value for a column in a table. When the user does not enter a value for the column (having default value), automatically the defined default value is inserted in the field.

When a column's value is skipped in an INSERT command, which value is inserted in the database ?

Answer

The columns that are not listed in the INSERT command will have their default value, if it is defined for them, otherwise, NULL value. If any other column (that does not have a default value and is defined NOT NULL) is skipped or omitted, an error message is generated and the row is not added.

Write MySql command to display the list of existing databases.

Answer

SHOW DATABASES;

Write MySql command will be used to open an already existing database "CONTACTS".

Answer

USE CONTACTS;

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 (a) and (b)
4. None

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.

With reference to SQL, identify the invalid data type.

1. Date
2. Integer
3. Year
4. Month

Answer

Year, Month

Reason — In SQL, 'Year' and 'Month' are not valid data types.

Which of the following is a DDL command ?

1. SELECT
2. ALTER
3. INSERT
4. UPDATE

Answer

ALTER

Reason — DDL (Data Definition Language) commands in SQL are used to create and define tables and other database objects. Examples of DDL commands include ALTER, which is used to modify objects like tables, indexes, views, and constraints. On the other hand, SELECT, INSERT, and UPDATE commands are part of DML (Data Manipulation Language), used for retrieving, inserting, and updating data within the database.

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.

Consider the following query :

SELECT name FROM class WHERE subject ............... NULL;

Which comparison operator may be used to fill the blank space in above query ?

1. =
2. LIKE
3. IS
4. IS NOT

Answer

IS, IS NOT

Reason — In SQL, to check for NULL values, we must use the IS NULL or IS NOT NULL comparison operators. The "=" operator cannot be used for NULL because NULL represents an unknown value, and comparisons using = (equal to) will not work as expected. The LIKE operator is used for pattern matching with strings.

In SQL, which command is used to SELECT only one copy of each set of duplicable rows ?

1. SELECT DISTINCT
2. SELECT UNIQUE
3. SELECT DIFFERENT
4. All of the above

Answer

SELECT DISTINCT

Reason — The SELECT DISTINCT statement is used in SQL to return only unique (distinct) values within the result set. It removes duplicate records, ensuring that each row in the result set is unique.

Which of the following types of table constraints will prevent the entry of duplicate rows ?

1. Unique
2. Distinct
3. Primary Key
4. NULL

Answer

Primary Key

Reason — A primary key is a set of one or more attributes or fields that uniquely identifies a tuple or row in a table. Therefore, it ensures that each row in the table is unique and prevents the entry of duplicate rows.

Ravisha has stored the records of all students of her class in a MYSQL table. Suggest a suitable SQL clause that she should use to display the names of students in alphabetical order.

1. SORT BY
2. ALIGN BY
3. GROUP BY
4. ORDER BY

Answer

ORDER BY

Reason — The suitable SQL clause that Ravisha should use to display the names of students in alphabetical order is ORDER BY. The ORDER BY clause in SQL is used to sort the result set of a query, and by default, it sorts the results in ascending order.

The USE command can be used to select an existing database in SQL.

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.

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

To refer to a set of values needed for a condition, we can use the SQL operator IN.

To exclude one or more values (a list of values) using a condition, the SQL keyword NOT IN should be used.

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

The UPDATE command can be used to make changes in the rows of a table in SQL.

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

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.

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. The UNIQUE and PRIMARY KEY constraints are similar but not the same.

Reason. There can be only one column with PRIMARY KEY constraint, in a 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
Both the UNIQUE and PRIMARY KEY constraints ensure unique values for each row in a column. However, UNIQUE allows NULL values, whereas PRIMARY KEY does not. There can exist multiple columns with UNIQUE constraints in a table, but only one column can have a PRIMARY KEY constraint.

Assertion. In terms of values allowed in a column, both UNIQUE and PRIMARY KEY constraints are not the same.

Reason. UNIQUE allows NULL value once in the column, but PRIMARY KEY does not.

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 UNIQUE and PRIMARY KEY constraints are similar in that they both ensure uniqueness of values in a column or set of columns. However, they differ in terms of the values they allow, particularly regarding NULL values. UNIQUE allows NULL values, whereas PRIMARY KEY does not.

Assertion. The INSERT INTO statement can skip some columns' values.

Reason. Only the columns allowing the NULL values or have default-value-defined, can be skipped in INSERT INTO statement of SQL.

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 INSERT INTO statement allows skipping columns values for columns that permit NULL values or have default values defined. If any other column (that does not have a default value and is defined as NOT NULL) is skipped or omitted, an error message is generated, and the row is not added.

Assertion. The PRIMARY KEY can be defined only once in the CREATE TABLE command.

Reason. If the PRIMARY KEY is a composite key, then it is not defined with the individual columns but at the end of the table definition as a table constraint.

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 SQL, when we define a table using the CREATE TABLE command, we can only specify one PRIMARY KEY constraint. This PRIMARY KEY can consist of a single column or multiple columns (composite key). When a PRIMARY KEY is a composite key (consisting of multiple columns), it's not defined with each individual column but rather as a table constraint at the end of the table definition.

Assertion. INSERT, UPDATE, DELETE, SELECT are the DML commands.

Reason. The DML commands manipulate the data stored in the database 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
INSERT, UPDATE, DELETE, and SELECT are the DML (Data Manipulation Language) commands in SQL. These commands manipulate the data stored in the database tables. INSERT adds new records, UPDATE modifies existing records, DELETE removes records, and SELECT retrieves data based on specified criteria.

Assertion. The PRIMARY KEY and FOREIGN KEY constraints are similar.

Reason. The FOREIGN KEY constraint links a column of a table with the PRIMARY KEY constraint of another 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
The PRIMARY KEY and FOREIGN KEY constraints are similar in that they both deal with unique identifiers. However, they serve different purposes, PRIMARY KEY uniquely identifies each record in a table while FOREIGN KEY establishes a relationship between two tables by linking a column (or set of columns) to the PRIMARY KEY of another table.

Assertion. Data types varchar and char are the same as they both represent the string data.

Reason. The VARCHAR datatype stores variable string length while CHAR datatype stores the string always as fixed length strings.

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
The VARCHAR and CHAR data types are not the same, as they differ in how they store string data. The VARCHAR data type stores variable-length string, while the CHAR data type stores strings as fixed-length character strings.

How are SQL commands classified ?

Answer

SQL commands can be divided into the following categories :

1. Data Definition Language (DDL) Commands
2. Data Manipulation Language (DML) Commands
3. Transaction Control Language (TCL) Commands
4. Session Control Commands
5. System Control Commands

Differentiate between DDL and DML commands.

Answer

Kabir has created the following table named exam :

Help him in writing SQL queries to perform the following tasks :

(i) Insert a new record in the table having following values : [6, 'Khushi', 'CS', 85]

(ii) To change the value "IP" to "Informatics Practices" in Subject column.

(iii) To remove the records of those students whose marks are less than 30.

(iv) To add a new column Grade of suitable datatype.

(v) To display records of "Informatics Practices" subject.

Answer

(i)

INSERT INTO exam(RegNo, Name, Subject, Marks)
VALUES(6, 'Khushi', 'CS', 85);

(ii)

UPDATE exam 
SET Subject = 'Informatics Practices' 
WHERE Subject = 'IP';

(iii)

DELETE FROM EXAM
WHERE MARKS < 30 ;

(iv)

ALTER TABLE EXAM
ADD COLUMN (Grade VARCHAR(1));

(v)

SELECT * FROM EXAM
WHERE Subject = "Informatics Practices";

What is the use of UPDATE statement in SQL ? How is it different from ALTER statement ?

Answer

The UPDATE statement is used to modify existing records in a table. It specifies the rows to be changed using the WHERE clause, and sets the new data using the SET keyword. In contrast, the ALTER statement is used to modify the structure of a table, such as adding, modifying, or deleting columns, and adding or dropping constraints.

Mr. Shankar created a table VEHICLE with 3 rows and 4 columns. He added 1 more row to it and deleted one column. What is the Cardinality and Degree of the Table VEHICLE ?

Answer

For the table VEHICLE, the cardinality is 4 and degree is 3.

Explanation — Initially, the table VEHICLE created by Mr. Shankar had 3 rows and 4 columns, with a cardinality of 3 and a degree of 4. After modifying the table by adding 1 row and deleting 1 column, the new cardinality of the table VEHICLE is 4 and the degree is 3.

Consider the following table named "GYM" with details about fitness items being sold in the store. Write command of SQL for (i) to (iv).

Table : GYM

(i) To display the names of all the items whose name starts with "A".

(ii) To display ICODEs and INAMEs of all items, whose Brandname is Reliable or Coscore.

(iii) To change the Brandname to "Fit Trend India" of the item, whose ICODE as "G101".

(iv) Add a new row for new item in GYM with the details :

"G107", "Vibro exerciser", 21000, "GTCFitness"

Answer

(i)

SELECT INAME 
FROM GYM 
WHERE INAME LIKE 'A%';

+-------------------+
| INAME             |
+-------------------+
| Aquafit Hand Grip |
+-------------------+

(ii)

SELECT ICODE, INAME 
FROM GYM
WHERE BRANDNAME = "Reliable" OR BRANDNAME = "Coscore";

+-------+----------------------+
| ICODE | INAME                |
+-------+----------------------+
| G102  | Aquafit Hand Grip    |
| G104  | Protoner Extreme Gym |
+-------+----------------------+

(iii)

UPDATE GYM
SET BRANDNAME = 'Fit Trend India'
WHERE ICODE = 'G101';

(iv)

INSERT INTO GYM 
VALUES("G107", "Vibro exerciser", 21000, "GTCFitness");

Mr. James created a table CLIENT with 2 rows and 4 columns. He added 2 more rows to it and deleted one column. What is the Cardinality and Degree of the Table CLIENT ?

Answer

For the table CLIENT, the cardinality is 4 and degree is 3.

Explanation — Initially, the table CLIENT created by Mr. James had 2 rows and 4 columns, with a cardinality of 2 and a degree of 4. After modifying the table by adding 2 rows and deleting 1 column, the new cardinality of the table CLIENT is 4 and the degree is 3.

Consider the following table FITNESS with details about fitness products being sold in the store. Write command of SQL for (i) to (iv).

Table : FITNESS

(i) To display the names of all the products with price more than 20000.

(ii) To display the names of all products by the manufacturer "Aone".

(iii) To change the price data of all the products by applying 25% discount reduction.

(iv) To add a new row for product with the details :

"P7", "Vibro Exerciser", 28000, "Aone".

Answer

(i)

SELECT PNAME FROM FITNESS WHERE PRICE > 20000;

+-----------+
| PNAME     |
+-----------+
| Treadmill |
| Multi Gym |
+-----------+

(ii)

SELECT PNAME 
FROM FITNESS 
WHERE MANUFACTURER = "Aone";

+-------+
| PNAME |
+-------+
| Bike  |
+-------+

(iii)

UPDATE FITNESS
SET PRICE = PRICE * 0.75;

(iv)

INSERT INTO FITNESS 
VALUES("P7", "Vibro Exerciser", 28000, "Aone");

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

Table : CLUB

(a) To show all information about the swimming coaches in the club.

(b) To list names of all coaches with their date of appointment (DATOFAPP) in descending order.

(c) To display a report, showing coachname, pay, age and bonus (15% of pay) for all the coaches.

Answer

(a)

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

(b)

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

(c)

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

(a) Select all the Nonmedical stream students from STUDENT1.

(b) List the names of those students who are in class 12 sorted by Stipend.

(c) List all students sorted by AvgMark in descending order.

Answer

(a)

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

(b)

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

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

(c)

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

What is foreign key ? How do you define a foreign key in your table ?

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. Defining a foreign key in a table involves specifying the relationship between the tables and setting up rules for data integrity. When two tables are related by a common column or set of columns, the related column(s) in the parent table (or primary table) should be either declared as a primary key or unique key. Meanwhile, the related column(s) in the child table (or related table) should have a foreign key constraint referencing the primary or unique key in the parent table.

How is FOREIGN KEY commands different from PRIMARY KEY command ?

Answer

The PRIMARY KEY is a set of one or more attributes that can uniquely identify tuples within the relation. A primary key column cannot contain NULL values, and it must have unique values for each row. Only one primary key constraint can exist per table. Conversely, the FOREIGN KEY command establishes a relationship between two tables by linking a column or set of columns in one table to the primary key or a unique key in another table. It enforces referential integrity, ensuring that values in the foreign key column(s) of the referencing table match values in the referenced table's primary key or unique key column(s). A foreign key can allow NULL values, indicating that the relationship is optional. Multiple foreign key constraints can exist in a table, each referencing a different parent table.

How is FOREIGN KEY commands related to the PRIMARY KEY ?

Answer

FOREIGN KEY commands establish relationships between tables by linking columns in one table to the PRIMARY KEY or a unique key in another table. This linkage ensures referential integrity, meaning that values in the FOREIGN KEY column(s) of the referencing table must match values in the PRIMARY KEY or unique key column(s) of the referenced table. Therefore, FOREIGN KEY commands are directly related to PRIMARY KEY commands as they rely on the unique identification provided by PRIMARY KEY constraints in other tables.

What are table constraints ? What are column constraints ? How are these two different ?

Answer

Table constraints are rules or conditions applied to an entire table in a database. They are defined when creating or altering a table's schema.

Column constraints are rules or conditions applied to individual columns within a database table. They are specified at the column level when creating or altering a table's schema.

The difference between the two is that column constraints apply only to individual columns, whereas table constraints apply to groups of one or more columns.

Insert all those records of table Accounts into table Pending where amt_outstanding is more than 10000.

Answer

INSERT INTO Pending
SELECT * FROM Accounts
WHERE amt_outstanding > 10000;

Increase salary of employee records by 10% (table employee).

Answer

Table employee

UPDATE employee
SET Salary = (Salary * 0.1) + Salary ;

To view all the details (all columns and rows) of the "employee" table the below query is executed :

SELECT * FROM employee ;

+----+------------+-----------+----------+--------+
| ID | First_Name | Last_Name | User_ID  | Salary |
+----+------------+-----------+----------+--------+
|  1 | Dim        | Joseph    | Jdim     |   5500 |
|  2 | Jaganath   | Mishra    | jnmishra |   4400 |
|  3 | Siddharth  | Mishra    | smishra  |   8800 |
|  4 | Shankar    | Giri      | sgiri    |   7700 |
|  5 | Gautam     | Buddha    | bgautam  |   2200 |
+----+------------+-----------+----------+--------+

Add a constraint (NN-Grade) in table Empl (given before assignment) that declares column Grade not null.

Answer

ALTER TABLE Empl
ADD CONSTRAINT NN_Grade 
(Grade NOT NULL) ;

Drop the table Empl.

Answer

DROP TABLE IF EXISTS Empl;

Differentiate between DROP TABLE, DROP DATABASE.

Answer

Differentiate between DROP TABLE, DROP clause of ALTER TABLE.

Answer

Mr. Mittal is using a table with following columns :

Name, Class, Stream_Id, Stream_name

He needs to display names of students who have not been assigned any stream or have been assigned stream_name that ends with "computers".

He wrote the following command, which did not give the desired result.

SELECT Name, Class FROM Students
WHERE Stream_name = NULL OR Stream_name = "%computers" ;

Help Mr. Mittal to run the query by removing the error and write correct query.

Answer

The error in Mr. Mittal's original query lies in using "= NULL" instead of "IS NULL" to check for NULL values and using = '%computers' instead of "LIKE '%computers'" for pattern matching.

The correct query is:

SELECT Name
FROM Students
WHERE Stream_name IS NULL OR Stream_name LIKE '%computers';

The Doc_name Column of a table Hospital is given below :

Based on the information, find the output of the following queries :

(i) SELECT doc_name FROM HOSPITAL WHERE Doc_name like "%v";

(ii) SELECT doc_name FROM HOSPITAL WHERE doc_name like "%e%";

Answer

(i) SELECT doc_name FROM HOSPITAL WHERE Doc_name like "%v";

+----------+
| doc_name |
+----------+
| Sanjeev  |
+----------+

The query SELECT doc_name FROM HOSPITAL WHERE Doc_name like "%v"; selects the doc_name from the HOSPITAL table where the Doc_name column ends with the letter "v" using the LIKE operator with the "%" wildcard. This pattern matches any string where "v" is the last character, and any characters can precede it.

(ii) SELECT doc_name FROM HOSPITAL WHERE doc_name like "%e%";

+----------+
| doc_name |
+----------+
| Deepak   |
| Sanjeev  |
+----------+

The query SELECT doc_name FROM HOSPITAL WHERE doc_name like "%e%"; selects the doc_name from the HOSPITAL table where the doc_name column contains the letter "e". This is achieved using the LIKE operator with the "%" wildcard before and after the letter "e", which matches any sequence of characters that have "e" in them.

Sarthak, a student of class XII, created a table "Class". Grade is one of the columns of this table. To find the details of students whose Grades have not been entered, he wrote the following MySql query, which did not give the desired result:

SELECT * FROM Class WHERE Grade = "Null";

Help Sarthak to run the query by removing the errors from the query and write the correct query.

Answer

The error in Sarthak's code is that he should use the IS NULL comparison instead of = "Null" because the correct syntax to check for NULL values in SQL is to use the IS NULL operator.

The correct query is:

SELECT * FROM Class WHERE Grade IS NULL;

What is the purpose of ALTER TABLE command ? Can you add new columns with constraints such as NOT NULL ? Give example to justify your answer.

Answer

The ALTER TABLE command is used to change definitions of existing tables. It is used to add columns, integrity constraints and redefine a column (datatype, size, default value) in a table.

Yes, we can add new columns with constraints such as NOT NULL, which ensures that a column must always contain a value (i.e., cannot be empty or null).

For example, to add a new column tel_number with the NOT NULL constraint in the Empl table, the statement is:

ALTER TABLE Empl
ADD COLUMN(tel_number integer NOT NULL);

What is the purpose of DROP TABLE command in MySql ? How is it different from DELETE command ?

Answer

The DROP TABLE command in MySql is used to permanently delete an entire table from the database, including its structure, data, indexes, triggers, and constraints. The DELETE command, on the other hand, is used to remove specific rows or all rows from a table, leaving the table structure intact.