SQL stands for Structured Query Language , and it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion and deletion of data from a database.
Q1. What is the difference between DELETE and TRUNCATE statements?
Delete :-
- Delete command is used to delete a row in a table.
- You can rollback data after using delete statement.t is a DML command.
- It is slower than truncate statement.
TRUNCATE
- Truncate is used to delete all the rows from a table.
- You cannot rollback data.
- It is a DDL command.It is faster.
Q2. What are the different subsets of SQL?
- DDL (Data Definition Language) – It allows you to perform various operations on the database such as CREATE, ALTER and DELETE objects.
- DML ( Data Manipulation Language) – It allows you to access and manipulate data. It helps you to insert, update, delete and retrieve data from the database.
- DCL ( Data Control Language) – It allows you to control access to the database. Example – Grant, Revoke access permissions.
3. What are different Clauses used in SQL?
WHERE Clause: This clause is used to define the condition, extract and display only those records which fulfill the given condition
Syntax: SELECT column_name(s)
FROM table_name
WHERE condition
SELECT * FROM Customers
WHERE Country='India';
WHERE Country='India';
GROUP BY Clause: It is used with SELECT statement to group the result of the executed query using the value specified in it. It matches the value with the column name in tables and groups the end result accordingly.
Syntax: SELECT column_name(s)
FROM table_name
GROUP BY column_name;
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID)
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID)
HAVING clause: This clause is used in association with the GROUP BY clause. It is applied to each group of result or the entire result as a single group and much similar as WHERE clause, the only difference is you cannot use it without GROUP BY clause
Syntax: SELECT column_name(s)
FROM table_name
GROUP BY column_name
HAVING condition;
The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
ORDER BY clause: This clause is to define the order of the query output either in ascending (ASC) or in descending (DESC) order. Ascending (ASC) is the default one but descending (DESC) is set explicitly.
Syntax: SELECT column_name(s)
FROM table_name
WHERE condition
ORDER BY column_name ASC|DESC;
SELECT * FROM Customers
ORDER BY Country;
ORDER BY Country;
SELECT * FROM Customers
ORDER BY Country DESC;
ORDER BY Country DESC;
USING clause: USING clause comes in use while working with SQL Joins. It is used to check equality based on columns when tables are joined. It can be used instead ON clause in Joins.
Syntax: SELECT column_name(s)
FROM table_name
JOIN table_name
USING (column_name);
4. SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:
- (INNER) JOIN: Returns records that have matching values in both tables
- LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
- RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
- FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
5. What are properties of the transaction?
Properties of transaction are known as ACID properties, such as
Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully if not then transaction is aborted at the failure point and the previous transaction is rolled back to its initial state as changes undone
Consistency: Ensures that all changes made through successful transaction are reflected properly on database
Isolation: Ensures that all transactions are performed independently and changes made by one transaction are not reflected on other
Durability: Ensures that the changes made in database with committed transactions persist as it is even after system failure
6. Aggregate functions we use in SQL
- AVG(): Returns the average value from specified columns
- COUNT(): Returns number of table rows
- MAX(): Returns largest value among the records
- MIN(): Returns smallest value among the records
- SUM(): Returns the sum of specified column values
- FIRST(): Returns the first value
- LAST(): Returns Last value
7. SQL Privileges?
SQL GRANT and REVOKE commands are used to implement privileges in SQL multiple user environments. The administrator of the database can grant or revoke privileges to or from users of database object like SELECT, INSERT, UPDATE, DELETE, ALL etc.
GRANT Command: This command is used provide database access to user apart from an administrator.
REVOKE Command: This command is used provide database deny or remove access to database objects.
8. What is the difference between DROP and TRUNCATE?TRUNCATE removes all rows from the table which cannot be retrieved back, DROP removes the entire table from the database and it cannot be retrieved back.
SELECT * FROM Student WHERE Student_Name like ‘K%’;
Here ‘like’ operator is used for pattern matching.
10. SQL query to find second highest salary ?
Highest salary :-
SELECT name, MAX(salary) as salary FROM employee
second largest salary
SELECT name, MAX(salary) AS salary
FROM employee
WHERE salary < (SELECT MAX(salary)
FROM employee);
FROM employee
WHERE salary < (SELECT MAX(salary)
FROM employee);
11. Select all records where Employee starts with ‘S’ and its length is 6 char.
select * from emp where Ename like'S____';
12. Select all records where Ename may be any no of character but it should end with ‘R’.
13.Select all records from Employee table whose name is ‘Ashish’ and ‘Anuj’.
Comments
Post a Comment