29.What is ACID property in SQL?
ACID properties are transactions properties which stands for Atomicity, Consistency, Isolation and durability. These features ensure consistent processing of database transactions:
Atomicity: Ensures that all operations are completed in a single transaction. If one part fails the entire transaction fails and the database state remains unchanged.
Consistency: Ensures that a database transaction changes from one valid state to another and maintains data integrity.
Isolation: Guarantees that transactions are executed independently and without interruption meaning that the intermediary state of one transaction is not visible to other transactions.
Durability: Guarantees that once a transaction is committed it lasts even if the system fails. These features are essential for transaction management in Oracle, MySQL, SQL Server and PostgreSQL and ensure robustness and reliability.
30. What is a constraint in SQL?
A constraint in SQL is a rule that is applied to a column or table to enforce data integrity. Constraints ensure that the data in the database follow certain rules and improve the accuracy and reliability of the data.
31.What are the types of constraints?
Common types of constraints are:
Primary key: Ensures a unique identity for each row.
Foreign key: Maintains referential integrity between tables.
Unique: Ensures that all values in the column are unique.
Not Null: Ensures that the column cannot contain a null value.
Check: Ensure that the values in the column match the specified conditions. Constraints are necessary to maintain data quality and reliability in databases such as Oracle, MySQL, SQL Server, and PostgreSQL.
32. What is a default constraint?
A default constraint in SQL specifies a default value for a column such that no value is provided during an insert operation. This ensures that the column has a meaningful value even if the user has not specified one. For example, if the Salary column has a default value of 5000, entering a new row without selecting Salary will automatically set it to 5000. Default constraints help maintain data consistency and simplify data entry. It is supported in Oracle, MySQL, SQL Server and PostgreSQL and allows seamless management of default values in database tables.
33. What is a check constraint?
A check constraint in SQL ensures that all values in a column meet certain conditions. Used to enforce domain integrity by limiting the values that can be stored in a column.
For example, a check constraint on the age column might ensure that all values are greater than 18. If a value is entered that does not meet the criteria the database will reject it. Constraints checking is a powerful tool to maintain data quality and prevent bad data from entering the database. It supports Oracle, MySQL, SQL Server and PostgreSQL.
34. What is a foreign key constraint?
A foreign key is a field (or group of fields) in one table that refers to a primary key in another table. A table with a foreign key is called a child table and a table with a primary key is called a parent table.
The purpose of foreign keys in a database management system (DBMS) is to ensure the integrity of the data in the database by preventing unauthorized changes. To maintain relationships between multiple tables and create useful links between them. To retrieve information from the database quicker and more proficiently.
35. What is a unique constraint?
A unique key, as the name suggests, enforces the uniqueness of values within a column or group of columns in a table. This do not allow duplicate value within a given key attribute(s) across all records in the table. A unique key in SQL can contain null values except in cases where the column has the additional constraint that it cannot be empty.
Syntax
Create table table_name(
Column_name2 datatype UNIQUE,
Column_name2 datatype);
36.What is a primary key constraint?
A primary key is a unique identifier that uniquely identify each rows in a table, such that no two records or row in the table are identical. The primary key does not allow NULL values and must be unique. The reason for this is that the primary key does not allow duplicate values and NULL.
A primary key can have one or more columns because you can use a concatenation of values as identifiers, as long as they are all unique.
A table can have only one primary key, which can contain of one or more columns or fields. When multiple columns are used as a primary key, then it is called a composite primary key. If a table has a primary key specified on any field(s), you cannot have two records with the same value for that field(s).
37. What is DDL?
DDL or Data Definition Language actually consists of SQL statements that can be used to define the database objects or schema. It simply deals with database schema descriptions and is used to create and modify the structure of database objects in the database.
DDL is a set of SQL statements used to create, modify, and delete database structures, but not data. These commands are not typically used by a general user who needs to access the database through an application.
CREATE: Create database or its objects (table, index, function, views, store procedure, and triggers)
ALTER: Alter the structure of the database existing database object, such as adding a column to a table.
DROP: Delete objects from the database.
TRUNCATE: Delete complete data or records permanently from a table without deleting the table structure.
38. What is DML?
DML Data Manipulation Language is Database language that allows you to insert, delete and update data in a database. DML is usually the language of a main database language such as SQL and DML contains some language operators. Data Manipulation Languageis a group of computer languages that provide instructions for manipulating data in databases.
Following are the some DML commands
INSERT:
This command is used to insert row or data into the table. INSERT INTO inserts the values listed in a table .
Syntax
INSERT INTO NAME_OF_TABLE (column-1, column-2, column-3, …. Column-N)
VALUES (value1, value2, value3, …. Value-N);
UPDATE:
This statement is used in SQL to update data in a table in the database. The UPDATE statement can be used to update one or more values in columns based on our specific requirements.
Syntax
UPDATE name_of_table SET coumn-1 = value-1, coumn-2 = value-2, coumn-3 = value-3,
… ,
coumn-N = value-N
WHERE condition;
And here,
name_of_table: name of the table
column-1, column-2, column-3, …. Column-N: name of the first, second, third, …. nth column.
value-1, value-2, value-3, …. value-N: the new value for the first, second, third, …. nth column.
condition: This condition is used to specify the rows whose column values should be updated.
DELETE:
The DELETE command can be used in SQL to delete different records from a table. Single or multiple records can be deleted based on the condition specified in the WHERE clause.
Syntax
DELETE FROM name-table WHERE condition;
39. What is DCL?
DCL stands for Data Control Language. this commands are used to control access to data in a database.
The basic DCL commands are:
GRANT - It is used to provide the user with access rights or other privileges to the database.
REVOKE- Used to take permissions back from the user.
40. What is TCL?
TCL: stands for Transaction Control Language. A subset of SQL used to manage transactions in a database. The basic commands of TCL are:
COMMIT: Saves all changes made to the current transaction to the database.
ROLLBACK: Roll back or undoes all changes made in the current transaction and return the database to its previous state.
Save point: Defines a point in the transaction that you can return to later. TCL data is important to maintain the consistency and integrity of data and allows users to confirm whether a series of operations have been successfully performed or not.
41. What is a schema in SQL?
A schema in SQL is a logical container for database objects such as tables, views, indexes and stored procedures. It helps organize and manage these elements in the database and provides a way to group them logically. Schemas can be used to manage permissions and access control and to simplify and manage user privileges.
For example in a university database different schemas may be used for student, course and faculty tables. The plans are supported by almost all databases like Oracle, MySQL, PostgreSQL and SQL Server and provide a systematic approach to database organization and security.
42. What is the difference between DELETE and TRUNCATE commands?
DELETE
DELETE is a command within the Data Manipulation Language (DML) category that is utilized to eliminate one or multiple rows or records from a table.
The DELETE statement contains a WHERE clause that specifies the condition for deleting only specific rows from the target table.
Upon executing a DELETE query a log file is generated in the transaction log to preserve the records before deletion, enabling the retrieval of crucial rows using the ROLLBACK command.
Since the DELETE statement is classified under the DML category, any modifications made must be manually made permanent.
It is essential to remember to execute a COMMIT before concluding a work session.
Syntax
DELETE FROM table-name WHERE condition;
TRUNCATE
TRUNCATE is a DDL (Data Definition Language) statement that is employed to delete all records from a table simultaneously and permanently.
In contrast to the DELETE command, the TRUNCATE command does not require any condition, such as the WHERE clause to specify the records to be deleted.
It should only be utilized when all the data in the target table is no longer necessary.
When utilizing the TRUNCATE command table locks and page locks are utilized instead of row locks for truncation operations.
This implies that it only logs the deallocation of the pages that store the records, resulting in significantly faster performance compared to the DELETE command.
Since the TRUNCATE statement is a DDL statement the commit is automatically executed
.
Additionally the TRUNCATE command resets the identity to its initial or seed value, or resets the high water mark.
Syntax
TRUNCATE TABLE table-name;
43.What is the difference between DROP and TRUNCATE commands?
Let's learn more about the difference between DROP and TRUNCATE commands.
DROP
In SQL, the DROP command is used to delete an entire database or index, data, and more. An important part of this command is the ability to permanently delete the table and its contents or rows.
TRUNCATE
The TRUNCATE command is used to delete all rows from the table. However, the structure of the table remains. This is faster than the DROP command.
Difference between DROP and TRUNCATE in SQL
DROP
1. It is used to delete or remove database or database objects such as Tables, Views, procedures, functions etc. permanently.
2. Drop table command also delete integrity constraints on associated table.
3. Drop command deletes data or row as well as structure of table.
4. It is slow as compared to the TRUNCATE command.
TRUNCATE
1. It is used to eliminate the tuples from the table.
2. Integrity constraint doesn’t get removed in the Truncate command.
3. Truncate delete all rows only.
4. It is fast as compared to the DROP command.
44. What is the difference between WHERE and HAVING clauses?
The difference between WHERE and HAVING clauses in SQL is that WHERE clause is used to specify a condition to filter records before doing any combination, while HAVING clause is used to specify a condition to filter values in a group. WHERE and HAVING clauses are two clauses in an SQL query that are used to filter records.
Where Clause
1. The WHERE Clause filters the records from the given table based On the specified condition.
2. The WHERE clause can be used without considering the GROUP BY Clause.
3. This Clause is implemented in row operations.
4. Where clause cannot contain aggregate function.
5. WHERE clause can be used in SELECT, UPDATE, DELETE statements.
6. This Clause is used before the GROUP BY Clause.
7. With the single-row function like UPPER, LOWER etc WHERE Clause is used.
Having Clause
1.The HAVING Clause filters the record from the given groups based On the specified condition.
2. The HAVING Clause cannot be used without considering the GROUP BY Clause.
3. This Clause is implemented in Grouping operation.
4. It can contain aggregate functions.
5. The HAVING Clause can be used with HAVING SQL.
6. This Clause is used after the GROUP BY Clause.
7. HAVING Clause is used with multiple row functions like SUM, COUNT etc.
45. What is the difference between UNION and UNION ALL?
UNION
The UNION operator is used to combine data from the results of two or more SELECT statements into a single result set. This operator removes any duplicates in the merged result.
1. It has the ability to eliminate duplicate rows from the table.
2. Here, the performance is slow because it also eliminates the duplicate rows.
3. Most of the people used Union operators.
4. Syntax:
SELECT column_1,column_2,column_n FROM table1
UNION
SELECT column_1,column_2,column_n FROM table2;
UNION ALL
The UNION ALL command helps us combine the results of two or more SELECT statements from different tables. UNION ALL statement contains duplicate records from SELECT statements while UNION statement does not include duplicate records otherwise both statements are same.
1. It cannot remove duplicate rows from the table.
2. Here, the performance is fast because it does not eliminate the duplicate values.
3. Hardly users use this operator.
4. Syntax:
SELECT column_list FROM table1
UNION ALL
SELECT column_list FROM table2;
46. What is the difference between COUNT(*),COUNT(1) and COUNT(column_name)?
COUNT(*)
The COUNT(*)returns the total number of rows in a table, including the NULLs. Let Say employees table have total 5 records in it. When count(*) applied it returns result 5 that shows the total records in that table.
Select Count(*) as CountRecords from employees;
COUNT(1)
The COUNT(1) function replaces all records in the query result with 1. If you have NULL values, they will also be replaced with 1. Therefore, COUNT(1) also returns the total number of records (including NULLs) in the given table.
Select Count(1) as CountRecords from Employees;
COUNT(column_name)
if a column name is specified in the SQL COUNT function argument, it counts the total number of rows in the table and do not count NULLs from the specified column.
Because NULL values can not be counted as these values are unknown.
For example, let’s use COUNT() function with ename and lname column.
If we provide a column name in the SQL COUNT function argument, it counts the total number of rows in the table and do not include NULLs in the specified column.
For example, let's use the COUNT() function with the ename and lname columns.
Select Count(ename) as NamewithoutNULL from employees;
NamewithoutNULL
5
Select Count(lname) as NamewithNULL from employees;
NamewithNULL
4
As shown above query result the first count function returns 5 while second returns 4 because it eliminated NULL value present in lname column.
