What Is SQL?
SQL (pronounced as the letters S-Q-L or as 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.
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. SQL statement can have both a WHERE clause and an 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.
What is BLOB datatype?
A BLOB data type is a binary string with a varying length which is used in storing two gigabytes memory. Length should be stated in Bytes for BLOB.
Difference between TRUNCATE & DROP
DROP TABLE – deletes the table from the database. TRUNCATE TABLE – empties it, but leaves the structure for future data.
What is DBMS?
A Database Management System (DBMS) is a program that controls 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.
What are the different types of SQL’s statements?
SQL statements are broadly classified into three. They are
DDL – Data Definition Language
DDL is used to define the structure that holds the data. For example, Create, Alter, Drop and Truncate table.
DML – Data Manipulation Language
DML is used for manipulation of the data itself. Typical operations are Insert, Delete, Update and retrieving the data from the table. The Select statement is considered as 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.
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.
What is a foreign key, unique key?
A foreign key is specified as a key which is related to the primary key of another table. Relationship needs to be created between two tables by referencing 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.
What is a constraint?
Constraint can be used to specify the limit on the data type of table. Constraint can be specified while creating or altering the table statement. Sample of constraint are.
NOT NULL.
PRIMARY KEY.
FOREIGN KEY.
Tell me about its various levels.
There are two levels:
column level constraint
table level constraint
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 transaction are released.
It undoes all the work done by the user in a transaction. With SAVEPOINT, only part of transaction can be undone.
What are the type of operators available in SQL?
Arithmetic operators
Logical operators
Comparison operator
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.
Which are the different types of indexes in SQL?
There are three types of Indexes in SQL:
Unique Index
Clustered Index
NonClustered Index
What is Union, minus and Intersect commands?
UNION operator is used to combine the results of two tables, and it eliminates duplicate rows from the tables.
MINUS operator is used to return rows from the first query but not from the second query. Matching records of first and second query and other rows from the first query will be displayed as a result set.
INTERSECT operator is used to return rows returned by both the queries.
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 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 logical order of data. Each table can have 999 non-clustered indexes.
What is the difference between SQL, MySQL and SQL Server?
SQL or Structured Query Language is a language which 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.
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 which are common between the tables and all rows of 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:
Left join is used to retrieve rows which are common between the tables and all rows of Left hand side table. It returns all the rows from Left hand side table even though there are no matches in the Right hand side table.
Full Join:
Full join return 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.
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.
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 type of stored procedures that are defined to execute automatically in place or after data modifications.