SQL

Question 1

What Is SQL

Answer 1

Structured Query Language

Question 2

What is rational database? State along with examples

Answer 2

The databases in which data is stored in tables. For Example MySQL, Oracle, PostgreSQL etc.

Question 3

What is non-relational database? State along with examples

Answer 3

The databases in which data is not stored in tables. For Example Mongo DB.

Question 4

What kind of functions does SQL perform?

Answer 4

CRUD
Create
Read
Update
Delete

Question 5

What is DBMS?

Answer 5

Database Management System

Question 6

State different data types primarily uses in SQL

Answer 6

1. Integer Data
2. Character (Text)
3. Monetary
4. Date and Time
5. Binary Strings

Question 7

State function used for integer data

Answer 7

INT(number) - Round down to nearest integer
TRUNC(number) - Cut off decimal, no rounding

Question 8

State function used for monetary data

Answer 8

DOLLAR(number, decimals) - Formats
number as currency (e.g.,
DOLLAR(1234.56, 2) = “$1 ,234.56”)

TEXT(number, “$#,##0.00”)- Custom currency formatting using symbols and
separators

Question 9

State function used for Character (Text)

Answer 9

CHAR(number) - Returns character for
an ASCII code (e.g., CHAR(65) = “A”)

TEXT(value, format) - Converts
numbers/dates to formatted text (e.g.,
TEXT(90, “0%”) = “90%”)

Question 10

Which functions are used for binary strings

Answer 10

DEC2BlN(number) - Converts decimal to
binary (e.g., DEC2BlN(5) = “101”)

BIN2DEC(binary) - Converts binary to
decimal (e.g., BIN2DEC(“101”)= 5)

Question 11

State functions used for date and time data

Answer 11

DATE(year, month, day) – Combines numbers into a date (e.g., DATE(2025, 4, 23))
TIME(hour, min, sec) – Combines time parts into a time value
NOW() / TODAY() – Current date & time / date only

Question 12

Query to create a database

Answer 12

CREATE DATABASE db name;

Question 13

Query to drop a database

Answer 13

DROP DATABASE db name;

Question 14

Query to use a particular database

Answer 14

USE db_name;

Question 15

List different types of data constraints in sql

Answer 15

1. NOT NULL Constraint.
2. UNIQUE Constraint.
3. DEFAULT Constraint.
4. CHECK Constraint.
5. PRIMARY KEY Constraint.
6. FOREIGN KEY Constraint.

Question 16

How to use UNIQUE constraint and what does it do?

Answer 16

Ensures all values in a column are unique. No duplicates allowed.
Example: email VARCHAR(100) UNIQUE

Question 17

How to use PRIMARY KEY constraint and what does it do?

Answer 17

Uniquely identifies each row in a table.
Combines NOT NULL + UNIQUE.
Example: id INT PRIMARY KEY

Question 18

How to use NOT NULL constraint and what does it do?

Answer 18

Ensures the column cannot have NULL values.
Every row must contain a value.

Example: name VARCHAR(100) NOT NULL

Question 19

How to use FOREIGN KEY constraint and what does it do?

Answer 19

Links a column to the primary key of another table.
Enforces referential integrity.
Example: student_id INT REFERENCES students(id)

Question 20

How to use CHECK constraint and what does it do?

Answer 20

Ensures all values in a column meet a specific condition.
Example: age INT CHECK (age 18)

Question 21

Query to write a table

Answer 21

USE db name;
CREATE TABLE table_name (
col_name1 datatype constraint,
col_name2 datatype constraint,
col_name3 datatype constraint
);

Question 22

How to insert values into a table

Answer 22

INSERT INTO db_name.table_name
(col1_name,col2_name)
VALUES
(1,”data1”),
(2,”data2”);

For Example
INSERT INTO first.table1
(id,name)
VALUES
(1,”Adam”),
(2,”Eve”);

Question 23

How to print all values of a table?

Answer 23

SELECT*FROM table_name;

Question 24

How to print selected value of a table?

Answer 24

SELECT col1,col2 FROM table_name:

Question 25

How to print distinct values of a table?

Answer 25

SELECT DISTINCT FROM table_name;

Question 26

What does WHERE clause do?

Answer 26

WHERE clause prints specific values of a table which satisfies a particular condition with the help of comparison / logical / arithmetic / bitwise operators E.g. SELECT * FROM table_name where id > 2 ;

Question 27

What does LIMIT clause do?

Answer 27

LIMIT clause sets an upper limit on number of (tuples)rows to be returned E.g. SELECT * FROM table_name LIMIT number;

Question 28

What does ORDER clause do?

Answer 28

It arranges values of a table in an ascending / descending order e.g. SELECT*FROM table_name ORDER BY col_names(s) ASC/DESC;

Question 29

What are aggregate functions? List different aggregate functions.

Answer 29

Aggregate functions perform a calculation on a set of values, and return a single value. 1. COUNT( ) 2. MAX() 3. MIN() 4. SUM() 5. AVG()

Question 30

What does GROUP BY clause do?

Answer 30

Groups rows that have the same values into summary rows. It collects data from multiple records and groups the result by one or more column. For Example, SELECT city, count (name) FROM student GROUP BY city;

Question 31

What does HAVING clause do?

Answer 31

Similar to Where i.e. applies some condition on rows. Used when we want to apply any condition after grouping. For Example, SELECT count (name), city FROM student GROUP BY city HAVING max(marks) > 90;

Question 32

What is the general order to write different clauses in a single query?

Answer 32

SELECT column(s) FROM table_name WHERE condition GROUP BY column(s) HAVING condition ORDER BY column(s) ASC/DESC ;

Question 33

How do you update existing rows in a table?

Answer 33

UPDATE table_name SET col1 = val1, col2 = val1 WHERE condition; For Example, UPDATE my table SET status = "Allowed" WHERE age >= 18;

Question 34

How to turn on Safe Mode in MySQL?

Answer 34

SET SQL_SAFE_UPDATES = 1;

Question 35

How to turn on Safe Mode in MySQL?

Answer 35

SET SQL_SAFE_UPDATES = 0;

Question 36

How to delete existing rows in a table?

Answer 36

DELETE FROM table_name WHERE condition; For Example, DELETE FROM userdata WHERE age<18;

Question 37

What is an EER diagram?

Answer 37

Enhanced Entity Relationship Diagram It helps you visualize how different tables are connected and related in your database.

Question 38

What is the syntax for using FOREIGN KEY

Answer 38

Will use an example here to get the grasp of it in a better way, CREATE TABLE country ( code INT PRIMARY KEY, country_name VARCHAR(100) ); CREATE TABLE userdata ( user_id INT PRIMARY KEY, user_name VARCHAR(100) user_country INT FOREIGN KEY (user_country) REFERENCES country (code) );

Question 39

Define parent and child table with the help of an example.

Answer 39

A parent is the table that stores the primary key, A child is any table that references the parent with a foreign key. For Example, Parent table: Students – holds student info. Child table: ExamScores – holds exam results linked to students. One student → many exam scores.

Question 40

What is ON DELETE CASCADE?

Answer 40

When we create a foreign key using this option, it deletes the referencing rows in the child table when the referenced row is deleted in the parent table which has a primary key.

Question 41

What is ON UPDATE CASCADE?

Answer 41

When we create a foreign key using UPDATE CASCADE the referencing rows are updated in the child table when the referenced row is updated in the parent table which has a primary key.

Question 42

How to add a column in a table?

Answer 42

ALTER TABLE table_name ADD COLUMN column_name datatype_constraint;

Question 43

How to delete a column in a table?

Answer 43

ALTER TABLE table_name DROP COLUMN column_name datatype_constraint;

Question 44

How to rename a existing table?

Answer 44

ALTER TABLE table_name RENAME TO new_table_name;

Question 45

How to rename an existing column?

Answer 45

ALTER TABLE table_name CHANGE COLUMN old_ name new_name new_datatype new_constraint;

Question 46

How to modify datatype/constraint of a column?

Answer 46

ALTER TABLE table_name MODIFY COL name new-datatype new-constraint;

Question 47

How to delete a table's data?

Answer 47

TRUNCATE table table_name;

Question 48

What is joins in SQL?

Answer 48

Joins is used to combine rows from two or more tables based on a related column between them.

Question 49

List different types of join.

Answer 49

1. Inner Join 2. Left Join 3. Right Join 4. Full Join

Question 50

How does inner join work?

Answer 50

For Example there are two tables Table A and B The data overlapping in both Table A and B will consist of inner join. SELECT column(s) FROM tableA INNER JOIN tableB ON tableA.col_name = tableB.col_name;

Question 51

How does left join work?

Answer 51

For Example there are two tables Table A and B Here, table A is left and B is right Now the left join will consist of Data overlapping between A and B and data which only exists in table A. SELECT column(s) FROM tableA LEFT JOIN tableB ON tableA.col_name = tableB.col_name;

Question 52

How does right join work?

Answer 52

For Example there are two tables Table A and B Here, table A is left and B is right Now the Right join will consist of Data overlapping between A and B and data which only exists in table B. SELECT column(s) FROM tableA RIGHT JOIN tableB ON tableA.col_name = tableB.col_name;

Question 53

How does full join work?

Answer 53

For Example there are two tables Table A and B Full join will consist of data existing in Table A and B both. SELECT * FROM student LEFT JOIN course ON student. id = course. id UNION SELECT * FROM student RIGHT JOIN course ON student. id = course. id;

Question 54

What is left exclusive join?

Answer 54

For Example there are two tables Table A and B Here, table A is left and B is right Left exclusive will only include values from table A excluding values overlapping between table A and B. For Example, SELECT * FROM tableA LEFT JOIN tableb ON tableA.col = tableB.col WHERE tableB.col IS NULL;

Question 55

What is right exclusive join?

Answer 55

For Example there are two tables Table A and B Here, table A is left and B is right Right exclusive will only include values from table B excluding values overlapping between table A and B. For Example, SELECT * FROM tableA RIGHT JOIN tableb ON tableA.col = tableB.col WHERE tableA.col IS NULL;