The following are the three subsets of SQL:
Data Definition Language (DDL) – It allows end-users to CREATE, ALTER, and DELETE database objects.
Data Manipulation Language (DML) – With this, you can access and manipulate data. It allows you to Insert, Update, Delete, and Retrieve data from the database.
Data Control Language (DCL) – This lets you control access to the database. It includes the Grant and Revoke permissions to manipulate or modify the database.
1. A primary key constraint uniquely identifies each row/record in a database table.
2. Primary keys must contain unique values.
3. Null value and duplicate values are not allowed to be entered in the primary key column.
4. A table can have only one primary key. It can consist of single or multiple fields.
A foreign key (referencing key) is used to link two tables together. It is a column or a combination of columns whose values match a Primary Key in a different table.
It acts as a cross-reference between tables because it references the primary key of another table and established a link between them.
There are three types of indexes in SQL:
1. Unique Index – It does not allow a field to have duplicate values if the column is unique indexed.
2. Clustered Index – This index defines the order in which data is physically stored in a table. It reorders the physical order of the table and searches based on key values. There can be only one clustered index per table.
3. Non-Clustered Index – It does not sort the physical order of the table and maintains a logical order of the data. Each table can have more than one non-clustered index.
This is one of the most commonly asked SQL interview questions.
SELECT * FROM MyTable WHERE MyColumn <> NULL
SELECT * FROM MyTable WHERE MyColumn IS NULL
The first syntax will not work because NULL means ‘no value’, and you cannot use scalar value operators. This is why there is a separate IS – a NULL predicate in SQL.
SQL commands are divided into the following categories –
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Language (DCL)
Transaction Control Language (TCL)
DDL commands include –
CREATE – Used to create the database or its objects like table, index, function, views, triggers, etc.
DROP – Used to delete objects
ALTER – Used to change database structures
TRUNCATE – It erases all records from a table, excluding its database structure
COMMENT – Used to add comments to the data dictionary
RENAME – Used to rename a database object
This is one of the most popularly asked SQL interview questions.
DML commands include –
SELECT – Used to select specific database data
INSERT – Used to insert new records into a table
UPDATE – It helps in updating existing records
DELETE – Used to delete existing records from a table
MERGE – Used to UPSERT operation (insert or update)
CALL – It is used when you need to call a PL/SQL or Java subprogram
EXPLAIN PLAN – Used to interpret data access path
LOCK TABLE – Used to control concurrency
DCL commands are –
GRANT – It provides user access privileges to the database
DENY – Used to deny permissions to users
REVOKE – Used to withdraw user access by using the GRANT command
DCL commands include –
COMMIT – Used to commit a transaction
ROLLBACK – Used to roll back a transaction
SAVEPOINT – Used to roll back the transaction within groups
SET TRANSACTION – Used to specify transaction characteristics
There are three types of Database Relationship –
One-to-one – Both tables can have only one record
One-to-many – The single record in the first table can be related to one or more records in the second table
Many-to-many – Each record in both the tables can be related to any number of records
There are six different types of Normalization –
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Fourth Normal Form (4NF)
Fifth Normal Form (5NF)
Ans: 6 triggers are allowed in the MySQL table.
Constraints are the representation of a column to enforce data entity and consistency.
There are two levels of constraint –
Column level – Limits only column data
Table level – Limits whole table data
Following are the most used constraints that can be applied to a table:
Data integrity attributes to the accuracy, completeness, and consistency of the data in a database. It also refers to the safety and security of data and is maintained by a collection of processes, rules, and standards that were implemented during the design phase.
Three types of data integrity are:
Referential integrity is a relational database concept that suggests that the accuracy and consistency of data should be maintained between primary and foreign keys.
ACID is short for Atomicity, Consistency, Isolation, Durability.
It ensures Data Integrity during a transaction.
Atomicity: It means either all the operations (insert, update, delete) inside a transaction take place or none.
Consistency: Consistency ensures that the data must meet all the validation rules. Irrespective of whatever happens in the middle of the transaction, Consistency property will never leave your database in a half-completed state.
Isolation: It means that every transaction is individual. One transaction can’t access the result of other transactions until the transaction is completed.
Durability: It implies that maintaining updates of committed transactions is important. It refers to the ability of the system to recover committed transaction in case of failure.
SQL string functions are used for string manipulation.
Following are the extensively used SQL string functions:
UPPER(): Converts character data to upper case
LOWER(): Converts character data to lower case
SUBSTRING() : Extracts characters from a text field
RTRIM(): Removes all whitespace at the end of the string
LEN(): Returns the length of the value in a text field
REPLACE(): Updates the content of a string.
LTRIM(): Removes all whitespace from the beginning of the string
CONCAT(): Concatenates function combines multiple character strings
CREATE TABLE new_table
AS (SELECT *
FROM old_table WHERE 1=2);
This will create a new table with the same structure as the old table with no rows copied.
This can be done by using ‘Update’ command with ‘LTRIM’ and ‘RTRIM’ function.
SET FullName = LTRIM(RTRIM(FullName));
Records can be fetched for odd and even row numbers:
Syntax to fetch even numbers:
Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=0
Syntax to fetch odd numbers:
Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=1
The syntax will be:
SELECT book_title FROM books LIMIT 15, 100.
The first number in LIMIT is the offset, and the second is the number.
The default port for the MySQL server is 3306.
To implement an ENUM column, use the given syntax:
CREATE TABLE table_name ( … col ENUM (‘value1′,’value2′,’value3’), … );
There are three groups of data types in MySQL, as listed below:
String Data Types – BINARY, VARBINARY, TINYBLOB, CHAR, NCHAR, VARCHAR, NVARCHAR, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, LONGBLOB, LONGTEXT, ENUM, SET, MEDIUMTEXT.
Numeric Data Types – MEDIUMINT, INTEGER, BIGINT, FLOAT, BIT, TINYINT, BOOLEAN, SMALLINT, DOUBLE, REAL, DECIMAL.
Date and Time Data Types – TIMESTAMP, TIME, DATE, DATETIME, YEAR.
InnoDB is the default storage engine in MySQL.
The NVL() function converts the Null value to the other value.
Joins are used to combine rows from two or more tables, based on a related column between them.
Types of Joins:
• INNER JOIN − Returns rows when there is a match in both tables.
• LEFT JOIN − Returns all rows from the left table, even if there are no matches in the right table.
• RIGHT JOIN − Returns all rows from the right table, even if there are no matches in the left table.
• FULL OUTER JOIN − Returns rows when there is a match in one of the tables.
• SELF JOIN − Used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
Example of Inner Join:
SELECT table1.col1, table2.col2,…, table1.coln
INNER JOIN table2
ON table1.commonfield = table2.commonfield;
Similarly you can apply all joins.
Q 31. Difference between DBMS and RDBMS?
RDBMS: Data is stored in table format
DBMS: Data is stored in file format
RDBMS: Multiple data elements are accessible together
DBMS: Individual access of data elements is possible
RDBMS: Data in the form of a table are linked together
DBMS: There is no connection between the data
RDBMS: It supports multiple users
DBMS: It supports single user
RDBMS: It features multiple level of security while handling data
DBMS: There is low security while handling data
RDBMS: Example: Oracle, SQL Server
DBMS: Example: XML, Microsoft Access
These questions will definitely help you in preparing for interviews.
All the Best!!!