SQL stands for Structured Query Language.SQL is a domain-specific programming language that allows you to query and manipulate data within database management systems arranged in an optimized manner and categorization. This is possible through implementing commands in SQL that allows you to read, write, select and delete entries or even columns of the same attribute or tables. SQL also provides a very efficient way of creating a dynamic accessway between your programs, websites, or mobile apps to a database. For example, by inputting your login details on a user website, these log information is passed on to a database by SQL for verification and user restriction.
A database is a structured collection of data for faster and better access, storage and manipulation of data.
A database can also be defined as collection of tables, schema, views and other database objects.
An RDBMS or Relational Database Management System is a type of DBMS having relationships between the tables using indexes and different constraints like primary key, foreign key etc. The use of indexes and constraints helps in faster retrieval and better management of data within the databases.
Data warehouse refers to a central repository of data from multiple sources of information. Those data are consolidated, transformed and made available for the mining as well as online processing.
The primary difference between DBMS and RDBMS is, in RDBMS we have relations between the tables of the database. Whereas in DBMS there is no relation between the tables(data may even be stored in files).
RDBMS has primary keys and data is stored in tables. DBMS has no concept of primary keys with data stored in navigational or hierarchical form.
RDBMS defines integrity constraints in order to follow ACID properties. While DBMS doesn’t follow ACID properties.
SQL is framed upon the structure of relational operations. It is based on certain modifications and enhancements.
A very basic SQL query form is:
select A1, A2, ..., An from R1, R2, ..., Rm where P
A table is a database object used to store records in a field in the form of columns and rows that holds data.
A field in a Database table is a space allocated to store a particular record within a table.
A tuple or record is an ordered set of related data item in a table.
SQL commands are the set of commands used to communicate and manage the data present in the database. The different type of SQL commands are-
- DDL – Data Definition Language
- DML – Data Manipulation Language
- DCL – Data Control Language
- TCL – Transactional Control Language
DDL commands are used to define or alter the structure of the database.
- CREATE: To create databases and database objects
- ALTER: To alter existing database objects
- DROP: To drop databases and databases objects
- TRUNCATE: To remove all records from a table but not its database structure
- RENAME: To rename database objects
Syntax:- (Create table) – Create table table_name(columname1 datatype(size), columname2 datatype(size),….);
Syntax:- (Alter table) – Alter table table_name add(columnname1 datatype(size), columname2 datatype(size),….)
Syntax:- (Drop) – alter table table_namedrop column col_name1; — drop ONE column
Syntax:- (Modify) – Alter table table_name modify(columnname1 datatype(size), columnname2 datatype(size),….);
Syntax:- (Drop)- Drop object object_name;
Drop table table_name;
Drop view view_name;
Syntax(Truncate)- truncate table table_name;
Syntax(Rename)- Alter table table_name rename column old column_name to new column_name; or Rename old table_name to new table_name;
DML commands are used for managing data present in the database.
- SELECT: To select specific data from a database
- INSERT: To insert new records into a table
- UPDATE: To update existing records
- DELETE: To delete existing records from a table
Syntax(Insert) – Insert into table_name values(values1, value2, value3,……);
Syntax(Update) – Update table_name set columnname=new value where columnname=old value;
Syntax(Delete)- Delete from table_name; OR Delete from tablename where condition;
DCL commands are used to create roles, grant permission and control access to the database objects.
- GRANT: To provide user access
- DENY: To deny permissions to users
- REVOKE: To remove user access
TCL commands are used to manage the changes made by DML statements.
- COMMIT: To write and store the changes to the database
- ROLLBACK: To restore the database since the last commit
The difference between the Delete, Truncate and Drop command is –
Delete command is a DML command, it removes rows from table based on the condition specified in the where clause, being a DML statement we can rollback changes made by delete command.
Truncate is a DDL command, it removes all the rows from table and also frees the space held unlike delete command. It takes lock on the table while delete command takes lock on rows of table.
Drop is a DDL command, it removes the complete data along with the table structure(unlike truncate command that removes only the rows).
SQL constraints are the set of rules that impose some restriction while insertion, deletion or update of data in the databases. In SQL we have both column level as well as table level constraints which are applied at columns and tables respectively. Some of constraints in SQL are – Primary Key, iForeign Key, Unique Key, Not NULL, DEFAULT, CHECK and Index constraint.
A unique constraint is used to ensure that the field/column will have only unique value(no duplication).
A primary key is a column or a combination of columns which uniquely identifies a record in the database. A primary key can only have unique and not NULL values and there can be only one primary key in a table.
Composite KEY is a primary key created on more than one column (combination of multiple fields) in a table.
A foreign key is used for enforcing referential integrity in which a field marked as foreign key in one table is linked with primary key of another table. With this referential integrity we can have only the data in foreign key which matches the data in the primary key of the other table.
CREATE TABLE Students ( /* Create table with foreign key - Way 1 */ ID INT NOT NULL Name VARCHAR(255) LibraryID INT PRIMARY KEY (ID) FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID) );
CREATE TABLE Students ( /* Create table with foreign key - Way 2 */ ID INT NOT NULL PRIMARY KEY Name VARCHAR(255) LibraryID INT FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID) );
ALTER TABLE Students /* Add a new foreign key */ ADD FOREIGN KEY (LibraryID) REFERENCES Library (LibraryID);
An index is used to speed up the performance of queries. It makes faster retrieval of data from the table. The index can be created on one column or a group of columns.
There are three types of indexes
1. Unique Index: Unique Indexes helps maintain data integrity by ensuring that no two rows of data in a table have identical key values. A unique index can be applied automatically when a primary key is defined. It ensures that the values in the index key columns are unique.
2. Clustered Index: Clustered Index reorders the physical order of the table and search based on the key values. There will be only one clustered index per table.
3. Non-Clustered Index: Non-Clustered Index doesn’t alter the physical order of the table and maintains a logical order of the data. Each table can have many non-clustered indexes.
The difference between the clustered and non-clustered index in SQL is as follows:
It is used for easy retrieval of data from the database and it is faster.
One table can only have one clustered index
It alters the way records are stored in a database as it sorts out rows by the column which is set to be clustered index.
It is slower compared to the Clustered index.
One table can have multiple non clustered index
It doesn’t alter the way it was sorted but it creates a separate object within a table which points back to the original table rows after searching.
A NULL value in SQL is an unknown or blank value. Since NULL is unknown value so, NULL value cannot be compared with another NULL values. Hence we cannot use ‘=’ operator in where condition with NULL. For this, we have IS NULL clause that checks if the value in field is NULL or not.
A Not NULL constraint is used for ensuring that the value in the field cannot be NULL.
A check constraint is used to limit the value entered in a field. E.g. we can ensure that field ‘Salary’ can only have value greater than 1000 using check constraint-
CREATE TABLE EMP_SALARY(EmpID int NOT NULL, NAME VARCHAR (30) NOT NULL, Salary INT CHECK (Salary > 1000), PRIMARY KEY (EmpID));
A Default constraint is used for providing a default value to a column when no value is supplied at the time of insertion of record in the database.
A view is like a subset of a table which is stored logically in a database. A view is a virtual table. It contains rows and columns similar to a real table. The fields in the view are fields from one or more real tables. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity.
CREATE VIEW view_name AS SELECT column_name1, column_name2 FROM table_name WHERE CONDITION;
Some of the advantages of Views are
Views occupy no space
Views are used to simply retrieve the results of complicated queries that need to be executed often.
Views are used to restrict access to the database or to hide data complexity.
Database Relationship is defined as the connection between the tables in a database. There are various database relationships namely
1. One to One Relationship
2. One to Many Relationship
3. Many to One Relationship
4. Self-Referencing Relationship
A database query is a request for data or information from a database table or combination of tables. A database query can be either a select query or an action query.
A Subquery is a SQL query within another query. It is a subset of a Select statement whose return values are used in filtering the conditions of the main query.
Data integrity defines the accuracy and consistency of the data stored in a database. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.
Auto increment keyword allows the user to create a unique number to get generated when a new record is inserted into a table. Auto increment keyword can be used whenever Primary Key is used.
The SQL SELECT DISTINCT query is used to return only unique values. It eliminates all the duplicated values.
‘Rename’ is a permanent name given to a table or column
‘Alias’ is a temporary name given to a table or column.
Join is a query, which retrieves related columns or rows from multiple tables.
Joins are used to combine records from multiple tables. The different types of joins in SQL are-
Inner Join – To fetch rows from two tables having matching data in the specified columns of both the tables.
SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
Left Join – To fetch all rows from left table and matching rows of the right table
SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
Right Join – To fetch all rows from right table and matching rows of the left table
SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
Full Outer Join – To fetch all rows of left table and all rows of right table
SELECT * FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
Self Join – Joining a table to itself, for referencing its own data
SELECT * FROM TABLE1 T1, TABLE1 T2 WHERE T1.columnA = T2.columnB;
A cross join returns cartesian product of the two tables, so there is no condition or on clause as each row of tabelA is joined with each row of tableB whereas a full outer join will join the two tables on the basis of condition specified in the on clause and for the records not satisfying the condition null value is placed in the join result.
SQL CLAUSE helps to limit the result set by providing a condition to an SQL Query. A CLAUSE helps to filter the rows from the entire set of records. SQL CLAUSES are WHERE & HAVING.
A ‘where’ clause is used to fetch data from database that specifies a particular criteria (specified after the where clause). Whereas a ‘having’ clause is used along with ‘GROUPBY’ to fetch data that meets a particular criteria specified by the aggregate function.
For example – for a table with Employee and Project fields, if we want to fetch Employee working on a particular project P2, we will use ‘where’ clause-
Select Employee from Emp_Project wh2ere Project = P2;
Now if we want to fetch Employees who are working on more than one project, we will first have to group the Employee column along with count of project and than the ‘having’ clause can be used to fetch relevant records-
Select Employee from Emp_Project GROUPBY Employee having count(Project)>1;
Both Union and Union All concatenate the result of two tables but the way these two queries handle duplicates are different.
Union: It omits duplicate records and returns only distinct result set of two or more select statements.
Union All: It returns all the rows including duplicates in the result set of different select statements.
Performance wise Union All is faster than Union, Since Union All doesn’t remove duplicates. Union query checks the duplicate values which consumes some time to remove the duplicate records.
Assume: Table1 has 10 records, Table2 has 10 records. Last record from both the tables are same.
If you run Union query.
SELECT * FROM Table1 UNION SELECT * FROM Table2
Output:- 19 records.
If you run UnionAll query.
SELECT * FROM Table1 UNION ALL SELECT * FROM Table2 Output:- 20 records.
Select into statement is used to directly select data from one table and insert into other, the new table gets created with same name and type as of the old table-
SELECT * INTO newtable FROM oldTable;
Yes, we can use ‘where’ clause with ‘GROUPBY’. The rows that doesn’t meet the where conditions are removed first and then the grouping is done based on the GROUPBY column.
SELECT Employee, Count(Project ) FROM Emp_Project WHERE Employee != 'A' GROUP BY Project;
By default, it is 1433
SQL aggregate functions return a single value, calculated from values in a column. Some of the aggregate functions in SQL are as follows
AVG() – This function returns the average value
COUNT() – This function returns the number of rows
MAX() – This function returns the largest value
MIN() – This function returns the smallest value
ROUND() – This function rounds a numeric field to the number of decimals specified
SUM() – This function returns the sum
SQL string functions are used primarily for string manipulation. Some of the widely used SQL string functions are
LEN() – It returns the length of the value in a text field
LOWER() – It converts character data to lower case
UPPER() – It converts character data to upper case
SUBSTRING() – It extracts characters from a text field
LTRIM() – It is to remove all whitespace from the beginning of the string
RTRIM() – It is to remove all whitespace at the end of the string
CONCAT() – Concatenate function combines multiple character strings together
REPLACE() – To update the content of a string.
Collation is defined as a set of rules that determine how character data can be sorted as well as compared. Character data is sorted using rules that define the correct character sequence along with options for specifying case-sensitivity, character width, accent marks, kana character types
Different types of collation sensitivity are as follows
Case Sensitivity: A and a and B and b.
Kana Sensitivity: Japanese Kana characters.
Width Sensitivity: Single byte character and double byte character.
Database normalisation is the process of organization of data in order to reduce the redundancy and anomalies in the database. We have different Normalisation forms in SQL like – First Normal Form, Second Normal Form, Third Normal Form and BCNF.
There are different types of Normalization forms in SQL.
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce and Codd Normal Form (BCNF)
According to First Normal Form a column cannot have multiple values, each value in the columns must be atomic.
For a table to be considered in Second Normal Form, it must follow 1NF and no column should be dependent on the primary key.
For a table to be Third Normal Form, it must follow 2NF and each non-prime attribute must be dependent on primary key of the table.
For each functional dependency X -> Y either-
X should be the super key or Y should be the prime attribute(part of one of the candidate keys) of table.
BCNF is the advanced or stricter version of 3NF.
For each functional dependency X -> Y-
X should be the super key
Denormalization is a database optimization technique used to increase the performance of a database infrastructure. It involves in the process of adding redundant data to one or more tables. In a normalized database, we store data in separate logical tables and attempt to minimize redundant data.
Transaction is a set of operations performed in a logical sequence. It is executed as a whole, if any statement in the transaction fails, the whole transaction is marked as failed and not committed to the database.
Locks in SQL are used for maintaining database integrity in case of concurrent execution of same piece of data.
The different types of locks in database are-
Shared locks – Allows data to be read-only(Select operations), prevents the data to be updated when in shared lock.
Update locks – Applied to resources that can be updated. There can be only one update lock on a data at a time.
Exclusive locks – Used to lock data being modified(INSERT, UPDATE, or DELETE) by one transaction thus ensuring that multiple updates cannot be made to the same resource at the same time.
Intent locks – A notification mechanism using which a transaction conveys that intends to acquire lock on data.
Schema locks- Used for operations when schema or structure of the database is required to be updated.
Bulk Update locks – Used in case of bulk operations when the TABLOCK hint is used.
A Trigger is a SQL procedure that initiates an action in response to an event (Insert, Delete or Update) occurs. When a new Employee is added to an Employee_Details table, new records will be created in the relevant tables such as Employee_Payroll, Employee_Time_Sheet etc.,
Scalar functions are the functions that return a single value by processing a single value in SQL. Some of the widely used SQL functions are-
UCASE() – USed to convert a string to upper case
LCASE() – Used to convert a string to lower case
ROUND() – Used to round a number to the decimal places specified
NOW() – Used to fetch current system date and time
LEN() – Used to find length of a string
SUBSTRING() or MID() – MID and SUBSTRING are synonyms in SQL. They are used to extract a substring from a string by specifying the start and end index. Syntax – SUBSTRING(ColumnName,startIndex,EndIndex).
LOCATE() – Used to find the index of the character in a string. Syntax – LOCATE(character,ColumnName)
LTRIM() – Used to trim spaces from left
RTRIM() – Used to trim spaces from right
Coalesce function is used to return the first not NULL value out of the multiple values or expressions passed to the coalesce function as parameters.Example-
COALESCE(NULL, NULL, 5, ‘ArtOfTesting’) will return the value 5.
COALESCE(NULL, NULL, NULL) will return NULL value as no not NULL value is encountered in the parameters list.
Cursors are objects in SQL that are used to traverse the result set of a SQL query one by one.
Orphan records are the records having foreign key to a parent record which doesn’t exist or got deleted.
In order to remove orphan records from database we need to create a join on the parent and child tables and then remove the rows from child table where id IS NULL.
DELETE PT FROM ParentTable PT LEFT JOIN ChildTable CT ON PT.ID = CT.ID WHERE PT.ID IS NULL
ACID properties refers to the four properties of transactions in SQL-
- Atomicity – All the operations in the transaction are performed as a whole or not performed at all.
- Consistency – State of database changes only on successful committed transaction.
- Isolation – Even with concurrent execution of the multiple transactions, the final state of the DB would be same as if transactions got executed sequentially. In other words each transaction is isolated from one another.
- Durability – Even in the state of crash or power loss the state of committed transaction remain persistent.
SELECT RIGHT(EmpName,5) AS EmployeeName FROM Employee
SELECT SUBSTRING(EmpName,1,5) AS EmployeeName FROM Employee
ALTER TABLE Employee_Details ADD (Incentive);
UPDATE Employee_Details set Incentive = 6500 where Employee_Name = ‘Ram’;
INSERT into Employee_Details (Employee_Name, Incentive, Age) VALUES (‘Raj’, 3000 , 24);
The SQL Delete statement is used to delete records from a table.
DELETE FROM table_name WHERE some_column=some_value;
SELECT FirstName, LastName FROM Employee_Details;
Select * from table where id % 2 = 0
Select * from table where id % 2 != 0
SQL pattern matching provides for pattern search in data if you have no clue as to what that word should be. This kind of SQL query uses wildcards to match a string pattern, rather than writing the exact word. The LIKE operator is used in conjunction with SQL Wildcards to fetch the required information.
1.Using the % wildcard to perform a simple search
The % wildcard matches zero or more characters of any type and can be used to define wildcards both before and after the pattern. Search a student in your database with first name beginning with the letter K:
SELECT * FROM students WHERE first_name LIKE 'K%'
2.Omitting the patterns using the NOT keyword
Use the NOT keyword to select records that don’t match the pattern. This query returns all students whose first name does not begin with K.
SELECT * FROM students WHERE first_name NOT LIKE 'K%'
3. Matching a pattern anywhere using the % wildcard twice
Search for a student in the database where he/she has a K in his/her first name.
SELECT * FROM students WHERE first_name LIKE '%Q%'
4.Using the _ wildcard to match pattern at a specific position
The _ wildcard matches exactly one character of any type. It can be used in conjunction with % wildcard. This query fetches all students with letter K at the third position in their first name.
SELECT * FROM students WHERE first_name LIKE '__K%'
5.Matching patterns for specific length
The _ wildcard plays an important role as a limitation when it matches exactly one character. It limits the length and position of the matched results. For example –
SELECT * /* Matches first names with three or more letters */ FROM students WHERE first_name LIKE '___%' SELECT * /* Matches first names with exactly four characters */ FROM students WHERE first_name LIKE '____'
Consider the following table.
|104||Asst. Manager||2017-06-11 01:00:00|
Select upper(FIRST_NAME) from Worker_details;
Select substring(FIRST_NAME,1,4) from Worker_details;
Select distinct length(DEPARTMENT) from Worker_details;
Select * from Worker_details order by FIRST_NAME asc,DEPARTMENT desc;
Select * from Worker_details where FIRST_NAME in ('Abhinay','Ranjana');
Select * from Worker_details where FIRST_NAME not in ('Abhinay','Ranjana');
Select * from Worker_details where FIRST_NAME like ‘%b%’;
Select * from Worker_details where FIRST_NAME like '___h';
Select * from Worker_details where FIRST_NAME like '%c';
Select * from Worker_details where year(JOINING_DATE) = 2015 and month(JOINING_DATE) = 2;
SELECT COUNT(*) FROM worker_details WHERE DEPARTMENT = 'Account';
Select INSTR(FIRST_NAME, BINARY'w') from Worker where FIRST_NAME = 'Jawad';
Select * from Worker_details where FIRST_NAME like '%a%';
Select * from Worker_details where SALARY between 10000 and 30000;
SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) As Worker_Name, Salary FROM worker_details WHERE WORKER_ID IN (SELECT WORKER_ID FROM worker_details WHERE Salary BETWEEN 50000 AND 100000);
SELECT DISTINCT W.FIRST_NAME, T.WORKER_TITLE FROM Worker_details W INNER JOIN Title T ON W.WORKER_ID = T.WORKER_REF_ID AND T.WORKER_TITLE in ('Manager');
SELECT DEPARTMENT, count(WORKER_ID) No_Of_Workers FROM worker_details GROUP BY DEPARTMENT ORDER BY No_Of_Workers DESC;
SELECT * FROM Worker_details WHERE MOD (WORKER_ID, 2) <> 0;
SELECT * FROM Worker WHERE MOD (WORKER_ID, 2) = 0;
(SELECT * FROM Worker_details) INTERSECT (SELECT * FROM WorkerClone_details);
Following MySQL query returns the current date:
Following MySQL query returns the current date and time:
Following SQL Server query returns the current date and time:
Following Oracle query returns the current date and time:
SELECT SYSDATE FROM DUAL;
Select distinct W.WORKER_ID, W.FIRST_NAME, W.Salary from Worker_details W, Worker_details W1 where W.Salary = W1.Salary and W.WORKER_ID != W1.WORKER_ID;
SELECT * FROM WORKER_detials WHERE WORKER_ID <= (SELECT count(WORKER_ID)/2 from Worker_details);
SELECT t.DEPARTMENT,t.FIRST_NAME,t.incentive from(SELECT max(Salary) as TotalSalary,DEPARTMENT from Worker_details group by DEPARTMENT) as TempNew Inner Join Worker t on TempNew.DEPARTMENT=t.DEPARTMENT and TempNew.TotalIncentive=t.Incentive;
SELECT distinct Incentive from worker_detials a WHERE 3 >= (SELECT count(distinct Incentive) from worker_details b WHERE a.Incentive <= b.Incentive) order by a.Incentive desc;
SELECT distinct Incentive from worker a WHERE 3 >= (SELECT count(distinct Incentive) from worker b WHERE a.Incentive >= b.Incentive) order by a.Incentive desc;
SELECT FIRST_NAME, Incentive from Worker WHERE Incentive=(SELECT max(Incentive) from Worker);