SQL

Question 1

What is the difference between DELETE and TRUNCATE statements?

Answer 1

DELETE
- The delete command is used to delete a row in a table
- You can rollback data after using the delete statement
- It is a DML command
- It is slower than the truncate statement

TRUNCATE
- Truncate is used to delete all the rows from a table
- You cannot rollback data
- It is a DDL command
- It is faster

Question 2

What are the different subsets of SQL?

Answer 2

DDL (Data Definition Language): Consists of the commands that can be used to change and modify the structure of a table
- CREATE
- ALTER
- DROP
- RENAME
- TRUNCATE

DML (Data Manipulation Language): Consists of the commands that deal with the manipulation of data present in the database
- INSERT
- UPDATE
- DELETE

DCL (Data Control Language): Includes the commands which deal with the rights, permissions, and other controls of the database system
- GRANT
- REVOKE

TCL (Transaction Control Language): Includes the commands which mainly deal with the transactions in a database
- COMMIT
- ROLLBACK
- SAVEPOINT

DQL (Data Query Language): Used to fetch data from the database
- SELECT

Question 3

What are joins in SQL?

Answer 3

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two table and retrieve data from there

Left Table: First table referenced
Right Table: Second table mentioned

Inner Join: Returns records that have matching values in both tables
Full Join: Returns all records when there is a single match in either the left or right table
Left Join: Returns all records from the left table, and the matching records from the right table
Right Join: Returns all records from the right table, and the matching records from the left table

Question 4

What is the difference between CHAR and VARCHAR datatype in SQL?

Answer 4

CHAR is used for strings of a fixed length
- Ex: char(10) can only store 10 characters and will not be able to store a string of any other length

VARCHAR is used for character strings of variable length
- Ex: varchar(10) can store any length under 10

Question 5

What is a Primary Key?

Answer 5

A single or group of fields or columns that can uniquely identify a row in a table

Question 6

What are constraints?

Answer 6

Constraints are used to specify the limit on the data type of the table. It can be specified when the table is created or altered.

Not Null: Ensures that a NULL value cannot be stored in the column
Unique: This constraint makes sure that all the values in a column
are different
Check: This constraint ensures that all the values in a column satisfy
a specific condition
Default: This constraint consists of a set of default values for a
column when no value is specified
Index: This constraint is used to create and retrieve data from the
database very quickly

Question 7

What is a Unique Key?

Answer 7

A unique key in SQL is the set of fields or columns of a table that helps us uniquely identify records. The unique key guarantees the uniqueness of the columns in the database. It is similar to the primary key but can accept a null value. A primary key is automatically a unique key. More than one unique key can exist in a table, while primary keys cannot

Question 8

What is a Foreign Key?

Answer 8

• A column or combination of columns that is used to establish and enforce a link between the data in two tables.
• The foreign key in the child table references the primary key in the parent table
• The foreign key constraint prevents actions that would destroy links between the child and parent table

Question 9

What is Data Integrity?

Answer 9

Accuracy and Consistency of data

Question 10

What function would you use to display the current date?

Answer 10

SELECT GETDATE();

Question 11

What is an Index?

Answer 11

Indexes are special tables used to increase the performance of data retrieval. This works like the index of a textbook, where you can use it to look up the topic of specific information and go straight to the page.

In this case, having an index for specific data can speed up the time it takes to use SELECT queries and WHERE clauses. They have no effect on the data as it is just a reference. Indexes take time to create and take up storage.

It is not wise to use indexes for small tables with a narrow range of values, or if the column has a large number of null values, or if the column is updated frequently because the indexes will also need to update alongside it.

Question 12

What is the difference between the DROP and TRUNCATE commands?

Answer 12

DROP removes a table and cannot be rolled back from the database
- Ex: DROP object object_name;

TRUNCATE removes all rows from the table and cannot be rolled back into the database
- Ex: TRUNCATE TABLE table_name;

Question 13

Explain the different types of Normalization

Answer 13

1NF - Each table cell should have a single value. So all records must be unique
2NF - Database should be 1NF and should depend on primary key column, partial dependencies placed in a separate table
3NF - Database should be in 2NF and must not have any transitive functional dependencies. Non primary key columns should not depend on non primary key columns

Question 14

What is ACID?

Answer 14

Atomicity: All or none; Either the whole transaction goes through or
none of it does
Consistency: The saved data cannot violate the integrity of the
database
Isolation: Keeps the transactions separated from each other until
they’re finished
Durability: System failures or restarts do not affect committed
transactions

Question 15

What is a Trigger in SQL?

Answer 15

Triggers in SQL are a special type of stored procedure that are defined to execute automatically in place or after data modifications. It allows you to execute a batch of code when an insert, update or any other query is executed against a specific table

Before Insert: Activated before data is inserted into the table
After Insert: Activated after date is inserted into the table
Before Update: Activated before data in the table is updated
After Update: Activated after the data in the table is updated
Before Delete: Activated before data is removed from the table
After Delete: Activated after data is removed from the table

Question 16

Are NULL values the same as that of zero or a blank space?

Answer 16

A NULL value is not at all the same as that of a zero or blank space

NULL value represents a value which is unavailable, unknown, assigned or not applicable whereas a zero is a number and blank space is a character

Question 17

What is a subquery in SQL?

Answer 17

A subquery is a query inside another query where a query is defined to retrieve data or information back from the database

Subqueries are always executed first and the result of the subquery is passed on to the main query

Question 18

Can you list the ways to get the count of records in a table?

Answer 18

SELECT * FROM table1;

SELECT COUNT(*) FROM table1;

SELECT rows FROM sysindexes WHERE id =
OBJECT_ID(table1) AND indid < 2

Question 19

Write a SQL query to find the names of employees that begin with ‘A’?

Answer 19

SELECT * FROM Table_name WHERE EmpName like’A%’

Question 20

What are group function commands in SQL?

Answer 20

AVG
COUNT
MAX
Min
SUM
VARIANCE

Question 21

What is the main difference between ‘Between’ and ‘IN’ condition operators?

Answer 21

BETWEEN
- Used to display rows based on a range of values in a row
- SELECT * FROM Students WHERE ROLL_NO BETWEEN 10 and 50

IN
- Used to check for values contained in a specific set of values
- SELECT * FROM students WHERE ROLL_NO IN (8, 15, 25);

Question 22

What is the difference between the ‘HAVING’ and ‘WHERE’ clauses?

Answer 22

HAVING CLAUSE
- Can be used only with SELECT statement. It is usually used in a GROUP BY clause

WHERE CLAUSE
- WHERE Clause is applied to each row before they are a part of the GROUP BY function in a query

Question 23

How can you fetch common records from two tables?

Answer 23

You can fetch common records from two tables using INTERSECT

SELECT column1, column2
FROM table_names
WHERE condition

INTERSECT

SELECT column1, column2
FROM table_names
WHERE condition

Question 24

What are aggregate and scalar functions?

Answer 24

Aggregate Functions
- Used to evaluate mathematical calculation and returns a single value. These calculations are done from the columns in a table
- Ex: MAX(), COUNT()

Scalar Functions
- Scalar functions return single value based on the input value
- Ex: UCASE(), NOW()

Question 25

What is a View?

Answer 25

A view is a virtual table which consists of a subset of data contained in a table. Since views are not present, it takes less space to store. Views can have data of one or more tables combined

A View is used for
- Restricting access to data
- Making complex queries
- Ensuring data independence
- Providing different views of the same data

Question 26

What is Auto Increment in SQL?

Answer 26

It allows the user to create a unique number to get generated whenever a new record is inserted into the table

The keyword is usually required whenever a primary key is used

Question 27

How can you convert a text into date format? Consider the given text as “31-01-2021”?

Answer 27

SELECT DATE_FORMAT(‘31-01-2021’, ‘%d-%m-%Y’) as date_value;