SQL Interview Questions
1. What is SQL ?
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.
2. What is database ?
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.
3. What is RDBMS ?
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.
4. What is Data warehouse ?
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.
5. Difference between DBMS and RDBMS.
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.
6. What is the Basic Structure of an SQL ?
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
7. What is table ?
A table is a database object used to store records in a field in the form of columns and rows that holds data.
8. What is field in a table ?
A field in a Database table is a space allocated to store a particular record within a table.
9. What is a tuple, record or row in a table.
A tuple or record is an ordered set of related data item in a table.
10. What are the different types of SQL commands ?
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
11. What are the different DDL commands in SQL ?
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;
12. What are different DML commands in SQL ?
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;
13. What are different DCL commands in SQL ?
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
14. What are the different TCL commands in SQL?
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
15. Difference between delete, truncate and drop command ?
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).
16. What are SQL constraints ?
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.
17. What is a Unique constraint ?
A unique constraint is used to ensure that the field/column will have only unique value(no duplication).
18. What is primary key ?
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.
19. What is composite key ?
Composite KEY is a primary key created on more than one column (combination of multiple fields) in a table.
20. What is Foreign key ?
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);
21. What is an index ?
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.
22. List down different types of indexes ?
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.
23. Difference between Cluster and Non-Cluster.
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.
24. What is Null Value ?
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.
25. What is not null constraint ?
A Not NULL constraint is used for ensuring that the value in the field cannot be NULL.
26. What is Check constraint ?
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));
27. What is Default constraint ?
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.
28. What is view ?
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;
29. List down advantages of view.
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.
30. What is a relationship and what are they ?
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
31. What is query ?
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.
32. What is subquery ?
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.
33. What is data Integrity ?
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.
34. What is Auto Increment in SQL ?
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.
35. How to avoid duplicate records in a query?
The SQL SELECT DISTINCT query is used to return only unique values. It eliminates all the duplicated values.
36 Difference between Renam and Alias ?
‘Rename’ is a permanent name given to a table or column
‘Alias’ is a temporary name given to a table or column.
37. What is a join ?
Join is a query, which retrieves related columns or rows from multiple tables.
38. List down types of joins in SQL ?
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;
39. Difference between cross join and full outer join.
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.
40. What is Clause in SQL.
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.
41. Difference between having and where clause.
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;
42. Difference between Union and Union All command.
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.
43. What is select into statement.
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;
44. Can we use ‘where’ clause with ‘GROUPBY’ ?
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;
45. Which TCP/IP port does SQL Server run ?
By default, it is 1433
46. What are aggregate function in SQL ?
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
47. What are string function in SQL ?
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.
48. What is Collation?
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
49. List down different types of collation sensitivity ?
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.
50. What is Database Normalisation ?
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.
51. What are all the different Normalization?
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)
52. Explain First Normal Form(1NF).
According to First Normal Form a column cannot have multiple values, each value in the columns must be atomic.
53. Explain Second Normal Form(2NF).
For a table to be considered in Second Normal Form, it must follow 1NF and no column should be dependent on the primary key.
54. Explain Third Normal Form (3NF).
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.
55. Explain Boyce and Codd Normal Form(BCNF).
BCNF is the advanced or stricter version of 3NF.
For each functional dependency X -> Y-
X should be the super key
56. What is Denormalization ?
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.
57. What are transactions in SQL ?
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.
58. What are locks is SQL ?
Locks in SQL are used for maintaining database integrity in case of concurrent execution of same piece of data.
59. List down types of locks in database.
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.
60. What is Trigger ?
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.,
61. What are scalar functions in SQL ?
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
62. What is coalesce function ?
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.
63. What are cursors in SQL ?
Cursors are objects in SQL that are used to traverse the result set of a SQL query one by one.
64. What are orphan records ?
Orphan records are the records having foreign key to a parent record which doesn’t exist or got deleted.
65. How can we remove orphan records from a table ?
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
66. What are ACID properties ?
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.
67. Write down command to fetch the first 5 characters of a string?
SELECT RIGHT(EmpName,5) AS EmployeeName FROM Employee
SELECT SUBSTRING(EmpName,1,5) AS EmployeeName FROM Employee
68. How to add ‘Incentive’ column to a Employee_Details table?
ALTER TABLE Employee_Details ADD (Incentive);
69. How to change the value of the field ‘Incentive’ as 6500 for an Employee_Name ‘Ram’ in a table Employee_Details ?
UPDATE Employee_Details set Incentive = 6500 where Employee_Name = ‘Ram’;
70. How to add new Employee details in a Employee_Details table with the following details Employee_Name: Raj, Incentive:3000, Age:24 ?
INSERT into Employee_Details (Employee_Name, Incentive, Age) VALUES (‘Raj’, 3000 , 24);
71. Define SQL Delete Statement.
The SQL Delete statement is used to delete records from a table.
DELETE FROM table_name WHERE some_column=some_value;
72. How to display current date in SQL ?
73. Write SQL SELECT query that returns the FirstName and LastName from Employee_Details table.
SELECT FirstName, LastName FROM Employee_Details;
74. How to select all the even number records from a table?
Select * from table where id % 2 = 0
75. How to select all the odd number records from a table?
Select * from table where id % 2 != 0
76. What is Pattern Matching in SQL ?
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|
77. Write an SQL Query to fetch “First Name” from Worker_details Table in Upper Case.
Select upper(FIRST_NAME) from Worker_details;
78. Write an SQL Query to print the first four Character of First_Name from Worker_details table.
Select substring(FIRST_NAME,1,4) from Worker_details;
79. Write an SQL Query that fetches the Unique Value of Department form Worker_details table and prints it value.
Select distinct length(DEPARTMENT) from Worker_details;
80. Write an SQL Query to print all Worker Details form the Worker_details table Order By First_Name Ascending and Department Descending.
Select * from Worker_details order by FIRST_NAME asc,DEPARTMENT desc;
81. Write an SQL Query to print details for workers with the First name as “Abhinay” And “Ranjana” from Worker_details Table.
Select * from Worker_details where FIRST_NAME in ('Abhinay','Ranjana');
82.Write an SQL Query to print details for workers Excluding First name as “Abhinay” And “Ranjana” from Worker_details Table.
Select * from Worker_details where FIRST_NAME not in ('Abhinay','Ranjana');
83. Write an SQL Query to print details of the workers whose First_Name Contains ‘B’.
Select * from Worker_details where FIRST_NAME like '%b%';
84. Write an SQL Query to print details of the worker whose First_Name Ends with ‘K’ and contains Four alphabets.
Select * from Worker_details where FIRST_NAME like '___h';
85. Write an SQL Query to print details of the worker whose First_Name Ends with ‘C’.
Select * from Worker_details where FIRST_NAME like '%c';
86. Write an SQL Query to print details of the worker who have joined In Feb’2015.
Select * from Worker_details where year(JOINING_DATE) = 2015 and month(JOINING_DATE) = 2;
87. Write an SQL Query to fetch the Count of Employee Working in the Department ‘Account’.
SELECT COUNT(*) FROM worker_details WHERE DEPARTMENT = 'Account';
88. Write an SQL Query to find the position of the Alphabet (‘W’) in the First Name Column ‘Jawad’ From Worker_details Table.
Select INSTR(FIRST_NAME, BINARY'w') from Worker where FIRST_NAME = 'Jawad';
89. Write an SQL Query to fetch details of the workers whose First_Name contains ‘K’.
Select * from Worker_details where FIRST_NAME like '%a%';
90. Write an SQL Query to fetch details of the worker whose Salary Lies Between 10000 And 30000.
Select * from Worker_details where SALARY between 10000 and 30000;
91. Write an SQL Query to fetch Worker Name with Salaries >= 40000 and <= 10000.
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);
92. Write an SQL Query to fetch Workers who are also Managers.
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');
93. Write an SQL Query to fetch the no. of workers for each department in the descending order.
SELECT DEPARTMENT, count(WORKER_ID) No_Of_Workers FROM worker_details GROUP BY DEPARTMENT ORDER BY No_Of_Workers DESC;
94. Write an SQL Query to show only Odd rows from a table.
SELECT * FROM Worker_details WHERE MOD (WORKER_ID, 2) <> 0;
95. Write an SQL Query to show only Even rows from a table.
SELECT * FROM Worker WHERE MOD (WORKER_ID, 2) = 0;
96. Write an SQL Query to Fetch Intersecting Records of two tables.
(SELECT * FROM Worker_details) INTERSECT (SELECT * FROM WorkerClone_details);
97. Write an SQL Query to Show the current date and time.
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;
98. Write an SQL Query to fetch the list of Employee with same salary.
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;
99. Write an SQL Query to fetch the first 50% records from a table.
SELECT * FROM WORKER_detials WHERE WORKER_ID <= (SELECT count(WORKER_ID)/2 from Worker_details);
100. Write an SQL Query to print the name of Employee having the highest incentive in each department.
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;
101. Write an SQL Query to fetch three Max Incentive from a table.
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;
102. Write an SQL Query to fetch three Min Incentive from a table.
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;
103. Write an SQL Query to Fetch the names of workers who earn the highest Salary.
SELECT FIRST_NAME, Incentive from Worker WHERE Incentive=(SELECT max(Incentive) from Worker);
I love to learn with kodnest it has all best information ☺️