1) What is SQL ?
SQL Stands for Sructured Query Language it is used for storing and Manageing data in Relational Database Management System (RDBMS). It is Standard Language for Relational Database System. It enables a user to Create, Read, Update and Delete.
2) What is Primary key and Foreign Key ?
A Primary Key is used to ensure that data in the specific column is unique. A column cannot have NULL values. It is either an existing table column or a column that is specifically generated by the database according to a defined sequence.
Example: STUD_NO, as well as STUD_PHONE, are candidate keys for relation STUDENT but STUD_NO can be chosen as the primary key (only one out of many candidate keys).
A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It is a column (or columns) that references a column (most often the primary key) of another table.
Example: STUD_NO in STUDENT_COURSE is a foreign key to STUD_NO in STUDENT relation.
3) What is Query in SQL ?
A Query is a SQL statement used to Retrive , Update and Manipulate data in database. The most common type of query is a SELECT Statement, which fetches data from one or more tables based on specified condtions.
4) What is Difference between WHERE and HAVING Clause ?
In SQL, the WHERE and HAVING clauses are essential for filtering data and refining query results. While both serve the purpose of applying conditions they are used at different stages of query execution and for distinct purposes. Understanding the differences between the WHERE and HAVING clauses is important for writing optimized and effective SQL queries. Where : Filters records before grouping. Having : Filter record after grouping.
| WHERE Clause | HAVING Clause |
|---|---|
| Filters rows before groups are aggregated. | Filters groups after the aggregation process.. |
| WHERE Clause can be used without GROUP BY Clause | HAVING Clause can be used with GROUP BY Clause |
| WHERE Clause implements in row operations | HAVING Clause implements in column operation |
| WHERE Clause cannot contain aggregate function | HAVING Clause can contain aggregate function |
| WHERE Clause can be used with SELECT, UPDATE, DELETE statement. | HAVING Clause can only be used with SELECT statement. |
| WHERE Clause is used before GROUP BY Clause | HAVING Clause is used after GROUP BY Clause |
| WHERE Clause is used with single row function like UPPER, LOWER etc. | HAVING Clause is used with multiple row function like SUM, COUNT etc. |
5) What is Normalization ?
It is a process of analyzing the given relation schemas based on their functional dependencies and Primary Keys to achieve the following desirable properties.
Minimizing Redundancy :- Minimizing the Insertion, Deletion, and Update Anomalies.
Normalization is a database design technique to remove redundant data.
6) What is Denormalization ?
Denormalization is a database optimization technique in which we add redundant data to one or more tables. this can help us avoid costly joins in a relational database. It is an optimization technique that is applied after normalization.
7) What is a join? and types of join ?
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Inner Join, Left Join, Right, Full join
SQL joins are the foundation of database management systems, enabling the combination of data from multiple tables based on relationships between columns. Joins allow efficient data retrieval, which is essential for generating meaningful observations and solving complex business queries.
SQL INNER JOIN :- Selects all the rows from the tables as long as the condition is satisfied.
LEFT JOIN :- Returns all the rows of the table on the left side of the join and matches rows for the table on the right side of the join. For the rows for which there is no matching row on the right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN.
RIGHT JOIN :-RIGHT JOIN returns all the rows of the table on the right side of the join and matching rows for the table on the left side of the join. It is very similar to LEFT JOIN for the rows for which there is no matching row on the left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.
SQL FULL JOIN :- FULL JOIN Creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both tables. For the rows for which there is no matching, the result-set will contain NULL values.
SQL Command
| DDL (Data Definition Language) | DML (Data Manipulation Language) | DCL(Data Control Language) | TCL(Transaction Control Language) | DQL(Data Query Language ) |
| 1) Create | 1) Insert | 1) Grand | 1) Commit | 1) Select |
| 2) Drop | 2) Update | 2) Revoke | 2) Rollback | |
| 3) Alter | 3) Delete | 3) Savepoint | ||
| 4) Truncate |