2. What are the different types of SQL commands?
SQL commands are similar to a table instruction. It is used to perform some operation with the database operations. It is also used to query specific function, tasks and data. SQL can perform various tasks such as creating tables, adding data to tables, removing or dropping tables, modifying tables, and specifying permissions for users.
These SQL commands are mainly classified into five categories:
DDL – Data Definition Language
DQL – Data Query Language
DML – Data Manipulation Language
DCL – Data Control Language
TCL – Transaction Control Language
Data Definition Language or DDL is really made up of SQL commands that are used to define the structure of a database. It simply deals with database schema descriptions and is used to create and modify the structure of database objects in a database.
DDL is a group of SQL commands mainly used to create, delete, and modify database structures and not used to change a data. These commands are not typically used by the average user who must access the database through an application.
DDL commands are:
CREATE
Create command is used to create the database or its objects table, function, indexes, views, triggers and stored procedures.
DROP
Delete objects from the database.
TRUNCATE
Delete or remove all records permanently from a table including all spaces allocated for the records.
ALTER
Alter the structure of the database.
RENAME
Rename an object existing in the database.
DQL (Data Query Language)
SELECT
A SELECT statement is used to read or select zero or more rows from one or more tables within a database or database views. In the majority of applications, SELECT is the most frequently utilized command for data manipulation (DML). Given that SQL is a declarative programming language, SELECT queries outline a result set but do not detail the method for its computation.
DML(Data Manipulation Language)
DML Data Manipulation Language is a subset of operations used to delete, insert and update data in a database. DML is often a sublanguage of a more complex language such as SQL.
List of DML commands
Some DML commands and their syntax are:
INSERT
Insert data into a table.
INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...);
UPDATE
Update existing data within a table.
UPDATE tablename SET column-1 = value-1, column-2 = value-2 WHERE condition;
DELETE
Delete records from a database table.
DELETE FROM table_name WHERE condition;
DCL (Data Control Language)
Data Control Language comprises commands such as REVOKE and GRANT which mainly deal with the permissions, rights, and other controls of the database system.
List of DCL commands
Two important DCL commands and their syntax are:
GRANT
Assigns new privileges to a user account, providing access to specific database objects, procedures, or functions.
GRANT privilege_type [List_of_columns)] ON [type_of_object] object_name TO user [WITH GRANT OPTION];
REVOKE
Removes previously granted privileges from a user account, taking away their access to certain database objects or actions.
REVOKE [GRANT OPTION FOR] privilege_type [(column_list)] ON [object_type] object_name FROM user [CASCADE];
TCL (Transaction Control Language)
Transactions is a group or set of tasks into a single unit of execution. Each transaction starts with a specific task and ends when all tasks in the batch are completed successfully. If any task fails, the transaction fails.
Therefore, it has only two outcomes: success or failure. You can learn more about transactions control language command here. The following are TCL commands.
List of TCL Commands
Some TCL commands and their syntax are:
COMMIT
Saves all changes permanently in database made during the transaction.
ROLLBACK
Undoes all changes made during the transaction.
SAVEPOINT
Creates a savepoint within the current transaction.
SAVEPOINT savepoint_name;
Oracle, MySQL, SQL Server, and PostgreSQL all support these categories, although the specific syntax and additional features may vary between them.
8. What is a composite key?
Two or more columns in a table are combined to form a composite key, which can be used to uniquely identify each row in the table. Row uniqueness is guaranteed whe Two or more columns in a table are combined to form a composite key, which can be used to uniquely identify each row in the table. Row uniqueness is guaranteed when columns are grouped together, but not when taken individually. It can also be known as a primary key is created by combining two or more attributes to uniquely identify each row in a table.
Note
A composite key can also be formed by combining multiple candidate keys.
A composite key cannot be null.
9. What is a database?
A database is information that is set up for easy management, access and updating. Computer databases naturally store sets of data files or records that cover information such as sales transactions, customer data, product information and financial information.
Databases are needed to store large amounts of data in one place. Using a database, organizations can quickly access, organize, modify, update, manage, and retrieve their data.
Databases are typically controlled using a database management system (DBMS). In any relational databases data is structured into tables containing of columns and rows. Many databases also use Structured Query Language (SQL) to record and query data. There are different types of databases, but the specific language used and the way it works depends on the type of database.
10. What is a table in SQL?
A relational database system comprises one or more objects called tables. The information or data of the database is stored in these tables. Tables are uniquely identified by name and consist of columns and rows. Columns contain the column name, data type, and any other attributes of the column. Rows contain records or data for columns. Below is an example of a table called "Weather".
City, State, low, high are the columns. Rows contain data for this table.
11. What is a view in SQL?
A view in SQL is like a virtual table that contains data from one or more tables. It does not store any data and does not physically exist in the database. The view name should not be duplicate in database similar to table in SQL. It covers a set of predefined SQL queries to fetch data from the database. It can also contain database tables from one or more databases.
Syntax to create a VIEW
CREATE VIEW ViewName AS
Select column1, Column2...Column N From tables
Where conditions;
SQL VIEW to fetch all records of a table.
CREATE VIEW EmpRecords
AS
SELECT *
FROM Employees;
12. What is an index in SQL?
SQL indexes are used to quickly retrieve data from a database. Table or view indexing is undoubtedly one of the best ways to improve the performance of queries and applications.
The SQL index is a quick lookup table for finding records that users need to search for frequently. The index is small, fast and optimized for quick searches.
This is very useful for joining relational tables and searching large tables.
SQL indexes are primarily a performance tool, so they really come into play if the database becomes large. SQL Database supports many types of indexes but one of the most common types is the clustered index.
This type of index is created automatically using the primary key.
13. What is normalization?
Normalization in database is defined as the process of organizing data . It involves creating tables and establishing relationships between those tables based on rules designed to protect data and increase database flexibility by eliminating redundancies and incompatible dependencies.
14. What are the different normal forms?
Normal forms help reduce data duplication, improve data consistency, and improve database performance.
When designing a database, it is important to find a balance between normalization and practicality while designing the database. On the other hand, advanced levels of normalization can lead to more complex database designs and queries.
First Normal Form (1NF): This is the basic level used in normalization. In 1NF, each table cell must contain only one value, and each column must have a unique name. First normal form helps eliminate duplicate data and simplify queries.
Second Normal Form (2NF): Second Normal form removes data redundancy by requiring each non-key attribute to depend on a primary key. This means that each column must be directly related to the primary key and not to other columns.
Third Normal Form (3NF): Third Normal Form 3NF builds on Second Normal Form 2NF by requiring that all non-key attributes be independent of each other. This means that each column must relate directly to the primary key and not to other columns in the same table.
Boyce Codd Normal Form (BCNF):
BCNF is a complex form of 3NF that makes each selector in the table a candidate key. In other words, BCNF ensures that each non-significant or non-key attribute is only associated with a candidate key.
Fourth Normal Form (4NF): 4NF Fourth normal form is a further improvement to BCNF which ensures that there are no multi-valued dependencies in the table.
Fifth Normal Form (5NF): Fifth Normal Form 5NF is the highest level of normalization, which involves dividing a table into smaller tables to eliminate data duplication and improve data integrity.
15. What is denormalization?
Denormalization is the process of merging normalized tables into larger tables to improve database read performance. This includes the deliberate introduction of redundancy by merging tables that were split during normalization. Denormalization can reduce the number of joins required in queries, thereby speeding up the data retrieval process. However, it can lead to increased storage requirements and more complex maintenance of data integrity.
For example, a denormalized database might combine customer and order information into a single table, which simplifies the query process but requires careful handling of additional data.
16. What is a join in SQL?
A join in SQL is an operation that joins rows from two or more tables based on a column joined between them. Join operations are used to retrieve data that spans multiple tables, allowing for more complex and meaningful queries. The most common types of joins are: inner join, left join, right join, and full join.
An inner join returns a row when both tables have a match.
LEFT JOIN returns all rows from the left table and matching rows from the right table and fills in null values from unmatched rows.
RIGHT JOIN is similar but includes all rows from the right table. FULL JOIN retrieves all rows when the rows matching a table.
Joins are fundamental in relational databases like Oracle, MySQL, SQL Server, and PostgreSQL for querying related data efficiently.
17. What are the different types of joins?
There are several types of joins in SQL:
INNER JOIN: Returns only rows with the same values in both tables.
RIGHT JOIN (RIGHT OUTER JOIN): All rows from the right table and the same rows from the left table. Null values are returned for the columns from the left table If there is no match.
LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and the matched rows from the right table. If there is no match, NULLs are returned for columns from the right table.
CROSS JOIN: is a type of join that returns the Cartesian product of the rows of the tables in the join. In other words, it combines every row in the first table with every row in the second table.
FULL JOIN (FULL OUTER JOIN): A full outer join is a way of joining tables so that the result contains matched and unmatched rows from both tables. Use FULL OUTER to join two tables when you want the result set to contain unmatched rows from both tables. The matching of records is based on the join condition.
SELF JOIN: Joins a table with itself. These join types are essential for constructing queries that pull related data from multiple tables in Oracle, MySQL, SQL Server, and PostgreSQL.
18. What is a self-join?
Self-join in SQL allows us to join a table with itself. This enables us to compare rows within the same table as if we were joining two different tables. Self joins are useful for comparing values in a hierarchical table, finding duplicate values, or comparing rows with other rows in the same table.
19. What is a cross join?
In SQL, CROSS JOIN is used to join each row of one table with each row of another table and to return the Cartesian product of the set of rows from the joined tables.
CROSS JOIN query is used in SQL to produce all combinations of records in two tables. For example, let say we have two columns: colour and size, and you need a result to display all potential combinations of them - this is where cross joins come in handy.
20. What is an inner join?
This method is used in SQL to connect or combine the rows of two or more tables based on the corresponding columns between them. Returns only rows that contain matching values in both tables involved in the join.
Syntax:
Select column_1, column_2,
...
FROM table_1
INNER JOIN table_2
ON table_1.column = table_2.column;
21. What is a left join?
SQL LEFT JOIN, also known as LEFT OUTER JOIN, is a type of SQL JOIN operation that retrieves all records from the left table (Table 1) and matching records from the right table (Table 2). If there are no matching records in the valid table, null values for those columns are included.
You can use SQL LEFT JOIN when you want to get all records from a table (left table) and match only records from a related table (right table).
Common uses of LEFT JOIN are:
Show All Items: When you want to display all items from the left table, even if there are no related items in the right table.
Handling Missing Data: When you need to deal with situations where data may be missing or incomplete in a table.
Optional Relationships: In situations where relationships between tables are optional, and you still want to display data from the base table.
Data combine: When you want to merge data from multiple tables while maintaining all the records from one of the table.
Syntax
SELECT column-list
FROM table1
LEFT JOIN
table2
ON table1.column=table2.column;
22. What is a right join?
A RIGHT OUTER JOIN is a technique built into SQL that combines records from two separate databases tables based on a specified criterion, ensuring that every record in the corresponding table is found in the result set. The final syntax is to define the left database and specify the join requirements using the ON clause, followed by selecting the correct table, keywords for right or right outer join, etc.
The basic requirement for a join is to specify a row match between two databases or tables. A right join in SQL returns each entry in the adjacent table and matching entries from the other side table. Null values are added to all columns in the left table as there are no results in the left table.
Syntax
SELECT *
FROM right_table
RIGHT JOIN left_table
ON right_table.column_name = left_table.column_name;
Where
SELECT: Specifies the columns you want to return. This join example selects each column from two tables.
FROM right_table: Points to the correct table where all rows will be retrieved.
RIGHT JOIN left_table: Indicates that you want to join every row in the right field and specify the match type as right outer join to the corresponding row in the left table.
ON right_table.column_name = left_table.column_name: Defines join criteria by specifying which columns on either side of the data set should be applied to match rows.
23. What is a full join?
A full join also known as a full outer join, is used to ensure that no data is lost when joining two tables. In this case, all rows from both tables are returned. If there are no values for a row in the table, they will be represented as NULL values as a result of the SQL join.
24. What is a subquery in SQL?
An SQL subquery is nothing but a query within another query. We use a subquery to retrieve data from two or more tables. A subquery is also often called as an inner query, while a statement containing a subquery is also called an outer select or outer query. We can execute or implement subqueries with the INSERT, SELECT, FROM, WHERE, UPDATE and DELETE statements using operators like =, <, >=,>, <=, IN, BETWEEN, etc.
25. What are the types of subqueries?
Subqueries can be divided into several types based on their use and structure:
Single-row subqueries:
Subqueries that return a single row as output from the original query or parent queryare called single row subqueries. Single-row subqueries are used in an SQL SELECT statement with a HAVING clause, a WHERE clause, or a FROM clause and a comparison operator.
Multi-row subqueries:
A multi-row subquery returns more or one rows to outer query . Operators used in single-row subqueries =, <>, >, >=, <, <= cannot be used in multi-row subqueries. As an alternative, other operator should be used.
Multi column subqueries:
Multiple column values of an inner query compared to multiple column values of an outer query are called multicolumn subqueries in the database.
Multi column subqueries allow you to combine repeated WHERE conditions into a single WHERE clause.
Column comparisons in a multi-column subquery can be pairwise or unpaired. You can use a subquery to do complex data operations.
syntax
SELECT * FROM table-name WHERE(COL1,COL2,…………) IN(SELECT COL1,COL2,……. FROM table-name;
Correlated subqueries:
A correlated subquery is defined as a subquery that holds a reference to a table that also appears in the outer query.
Non-correlated subqueries:
A non-correlated subquery is defined as a subquery that can be executed autonomously or independently of an outer query.
In correlate subquery a subquery does not depend on an outer query for its results.
correlated subqueries are typically used to retrieve a value or set of values used in the WHERE clause or HAVING clause of an outer query.
