Question 1

Question 1Assertion. 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.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true but R is not the correct explanation of A.ExplanationBoth 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.

Question 2

Question 2Assertion. 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.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true and R is the correct explanation of A.ExplanationThe 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.

Question 3

Question 3Assertion. 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.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true and R is the correct explanation of A.ExplanationThe 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.

Question 4

Question 4Assertion. 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.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true and R is the correct explanation of A.ExplanationIn 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.

Question 5

Question 5Assertion. INSERT, UPDATE, DELETE, SELECT are the DML commands.Reason. The DML commands manipulate the data stored in the database tables.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true and R is the correct explanation of A.ExplanationINSERT, 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.

Question 6

Question 6Assertion. 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.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true but R is not the correct explanation of A.ExplanationThe 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.

Question 7

Question 7Assertion. 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.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

A is false but R is true.ExplanationThe 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.

Question 8

Question 1How are SQL commands classified ?

Accepted Answer

SQL commands can be divided into the following categories :Data Definition Language (DDL) CommandsData Manipulation Language (DML) CommandsTransaction Control Language (TCL) CommandsSession Control CommandsSystem Control Commands

Question 9

Question 2Differentiate between DDL and DML commands.

Accepted Answer

Data Definition Language (DDL)Data Manipulation Language (DML)DDL provides a set of definitions to specify the storage structure and access methods used by the database system.DML is a language that enables users to access or manipulate data as organized by the appropriate data model.DDL commands are used to perform tasks such as creating, altering, and dropping schema objects. They are also used to grant and revoke privileges and roles, as well as for maintenance commands related to tables.DML commands are used to retrieve, insert, delete, modify data stored in the database.Examples of DDL commands are CREATE, ALTER, DROP, GRANT, ANALYZE etc.Examples of DML commands are INSERT, UPDATE, DELETE, SELECT etc.

Question 10

Question 3Kabir has created the following table named exam :RegNoNameSubjectMarks1SanyaComputer Science1982SanchayIP1003VineshCS904SnehaIP995AkshitaIP100Help 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.

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

Question 11

Question 4(a)What is the use of UPDATE statement in SQL ? How is it different from ALTER statement ?

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

Question 12

Question 4(b)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 ?

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

Question 13

Question 4(c)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 : GYMICODEINAMEPRICEBRANDNAMEG101Power Fit Exerciser20000Power GymeaG102Aquafit Hand Grip1800ReliableG103Cycle Bike14000EcobikeG104Protoner Extreme Gym30000CoscoreG105Message Belt5000Message ExpertG106Cross Trainer13000GTC Fitness(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"

Accepted Answer

(i)SELECT INAME 
FROM GYM 
WHERE INAME LIKE 'A%';Output+-------------------+
| INAME             |
+-------------------+
| Aquafit Hand Grip |
+-------------------+
(ii)SELECT ICODE, INAME 
FROM GYM
WHERE BRANDNAME = "Reliable" OR BRANDNAME = "Coscore";Output+-------+----------------------+
| 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");

Question 14

Question 5(a)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 ?

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

Question 15

Question 5(b)Consider the following table FITNESS with details about fitness products being sold in the store. Write command of SQL for (i) to (iv).Table : FITNESSPCODEPNAMEPRICEManufacturerP1Treadmill21000CoscoreP2Bike20000AoneP3Cross Trainer14000ReliableP4Multi Gym34000CoscoreP5Massage Chair5500RegroseneP6Belly Vibrator Belt6500Ambawya(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".

Accepted Answer

Question 16

Question 6Write SQL commands for the following on the basis of given table CLUB.Table : CLUBCOACH_IDCOACHNAMEAGESPORTSDATOFAPPPAYSEX1KUKREJA35KARATE27/03/19961000M2RAVINA34KARATE20/01/19981200F3KARAN34SQUASH19/02/19982000M4TARUN33BASKETBALL01/01/19981500M5ZUBIN36SWIMMING12/01/1998750M6KETAKI36SWIMMING24/02/1998800F7ANKITA39SQUASH20/02/19982200F8ZAREEN37KARATE22/02/19981100F9KUSH41SWIMMING13/01/1998900M10SHAILYA37BASKETBALL19/02/19981700M(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.

Accepted Answer

(a)SELECT *
FROM CLUB 
WHERE SPORTS = 'SWIMMING' ;Output+----------+-----------+-----+----------+-----+-----+------------+
| 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 ;Output+-----------+------------+
| 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 ;Output+-----------+------+-----+--------+
| 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 |
+-----------+------+-----+--------+

Question 17

Question 7Write SQL commands for the following on the basis of given table STUDENT1.Table : STUDENT1No.NameStipendStreamAvgMarkGradeClass1Karan400.00Medical78.5B12B2Divakar450.00Commerce89.2A11C3Divya300.00Commerce68.6C12C4Arun350.00Humanities73.1B12C5Sabina500.00Nonmedical90.6A11A6John400.00Medical75.4B12B7Robert250.00Humanities64.4C11A8Rubina450.00Nonmedical88.5A12A9Vikas500.00Nonmedical92.0A12A10Mohan300.00Commerce67.5C12C(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.

Accepted Answer

(a)SELECT *
FROM STUDENT1
WHERE Stream = 'Nonmedical' ;Output+-----+--------+---------+------------+---------+-------+-------+
| 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 ;Output+--------+
| Name   |
+--------+
| Divya  |
| Mohan  |
| Arun   |
| Karan  |
| John   |
| Rubina |
| Vikas  |
+--------+
(c)SELECT *
FROM STUDENT1
ORDER BY AvgMark DESC ;Output+-----+---------+---------+------------+---------+-------+-------+
| 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   |
+-----+---------+---------+------------+---------+-------+-------+

Question 18

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

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

Question 19

Question 9How is FOREIGN KEY commands different from PRIMARY KEY command ?

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

Question 20

Question 10How is FOREIGN KEY commands related to the PRIMARY KEY ?

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

MySQL SQL Revision Tour

Assertions And Reasons

Assignment

Checkpoint 51

Fill In The Blanks

Multiple Choice Questions

Truefalse Questions