1. What Is SQL?
SQL (pronounced as the letters S-Q-L or as a sequel) is an abbreviation for Structured Query Language. SQL is a language designed specifically for communicating with databases. SQL is designed to do one thing and do it well—provide you with a simple and efficient way to read and write data from a database.
2. Explain SQL Having clause.
The SQL HAVING clause allows us to restrict the data that is sent to the GROUP BY clause.
Group functions cannot be used in the WHERE clause. An SQL statement can have both a WHERE clause and a HAVING clause. WHERE filters data before grouping and HAVING filters the data after grouping.
A WHERE clause is useful in both grouped and ungrouped queries, while a HAVING clause should appear only immediately after the GROUP BY clause in a grouped query.
HAVING statement in SQL specifies that a SQL SELECT statement should only return rows where aggregate values meet the specified conditions.
An SQL statement with the HAVING clause may or may not include the GROUP BY clause.
HAVING allows a user to perform conditional tests on aggregate values. It is often used in combination with GROUP BY. With HAVING, you can include or exclude groups based on the aggregate value for that group.
3. What is BLOB datatype?
A BLOB data type is a binary string with a varying length that is used in storing two gigabytes of memory. Length should be stated in Bytes for the BLOB.
4. Difference between TRUNCATE & DROP
DROP TABLE – deletes the table from the database. TRUNCATE TABLE – empties it, but leaves the structure for future data.
5. What is DBMS?
A Database Management System (DBMS) is a program that controls the creation, maintenance, and use of a database. DBMS can be termed as File Manager that manages data in a database rather than saving it in file systems.
6. What are the different types of SQL’s statements?
SQL statements are broadly classified into three. They are
1. DDL – Data Definition Language
DDL is used to define the structure that holds the data. For example, Create, Alter, Drop, and Truncate table.
2. DML – Data Manipulation Language
DML is used for the manipulation of the data itself. Typical operations are Insert, Delete, Update, and retrieve the data from the table. The Select statement is considered a limited version of the DML since it can’t change the data in the database. But it can perform operations on data retrieved from the DBMS before the results are returned to the calling function.
3. DCL – Data Control Language
DCL is used to control the visibility of data like granting database access and set privileges to create tables, etc. Example – Grant, Revoke access permission to the user to access data in the database.
7. What is a foreign key, unique key?
A foreign key is specified as a key that is related to the primary key of another table. The relationship needs to be created between two tables by referencing a foreign key with the primary key of another table.
A Unique key constraint uniquely identifies each record in the database. This provides uniqueness for the column or set of columns.
8. What is a constraint?
A constraint can be used to specify the limit on the data type of table. The constraint can be specified while creating or altering the table statement. Sample of constraint is.
- NOT NULL.
- CHECK.
- DEFAULT.
- UNIQUE.
- PRIMARY KEY.
- FOREIGN KEY.
9. Tell me about its various levels.
There are two levels:
- column level constraint
- table level constraint
10. Explain Commit, Rollback and Savepoint.
For a COMMIT statement, the following is true:
Other users can see the data changes made by the transaction.
The locks acquired by the transaction are released.
The work done by the transaction becomes permanent.
A ROLLBACK statement gets issued when the transaction ends, and the following is true.
The work done in a transition is undone as if it was never issued.
All locks acquired by the transaction are released.
It undoes all the work done by the user in a transaction. With SAVEPOINT, only part of the transaction can be undone.
11. What are the type of operators available in SQL?
- Arithmetic operators
- Logical operators
- Comparison operator
12. What is the difference between BETWEEN and IN condition operators?
The BETWEEN operator is used to display rows based on a range of values. The IN condition operator is used to check for values contained in a specific set of values.
13. Which are the different types of indexes in SQL?
There are three types of Indexes in SQL:
- Unique Index
- Clustered Index
- NonClustered Index
14. What is Union, minus and Intersect commands?
UNION operator is used to combining the results of two tables, and it eliminates duplicate rows from the tables.
MINUS operator is used to returning rows from the first query but not from the second query. Matching records of the first and second query and other rows from the first query will be displayed as a result set.
INTERSECT operator is used to returning rows returned by both the queries.
15. What is Unique Index, Clustered Index and NonClustered Index?
Unique Index:
This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when the primary key is defined.
Clustered Index:
The clustered index is used to reorder the physical order of the table and search based on the key values. Each table can have only one clustered index.
NonClustered Index:
NonClustered Index does not alter the physical order of the table and maintains the logical order of data. Each table can have 999 non-clustered indexes.
16. What is the difference between SQL, MySQL and SQL Server?
SQL or Structured Query Language is a language that is used to communicate with a relational database. It provides a way to manipulate and create databases. On the other hand, MySQL and Microsoft’s SQL Server both are relational database management systems that use SQL as their standard relational database language.
17. What is Inner Join, Right Join, Left Join and Full Join in SQL?
Inner join:
Inner join returns rows when there is at least one match of rows between the tables.
Right Join:
Right, join is used to retrieve rows that are common between the tables and all rows of the Right-hand side table. It returns all the rows from the right-hand side table even though there are no matches in the left-hand side table.
Left Join:
A left join is used to retrieve rows that are common between the tables and all rows of the Left-hand side table. It returns all the rows from the Left-hand side table even though there are no matches in the Right-hand side table.
Full Join:
Full join returns rows when there are matching rows in any one of the tables. This means it returns all the rows from the left-hand side table and all the rows from the right-hand side table.
18. Which operator is used in query for pattern matching?
LIKE operator is used for pattern matching, and it can be used as -.
% – Matches zero or more characters.
_(Underscore) – Matching exactly one character.
19. What is “TRIGGER” in SQL?
Trigger allows you to execute a batch of SQL code when an insert, update or delete command is executed against a specific table.
Actually, triggers are special types of stored procedures that are defined to execute automatically in place or after data modifications.
SQL (pronounced as the letters S-Q-L or as a sequel) is an abbreviation for Structured Query Language. SQL is a language designed specifically for communicating with databases. SQL is designed to do one thing and do it well—provide you with a simple and efficient way to read and write data from a database.
