sql questions in interview code example
Example 1: sql interview questions
/* Answer to: "sql interview questions" */
/*
Here are 50 common questions you may be asked in an interview!
Go to https://www.guru99.com/sql-interview-questions-answers.html
for the answers of each question.
1. What is DBMS?
2. What is RDBMS?
3. What is SQL?
4. What is a Database?
5. What are tables and Fields?
6. What is a primary key?
7. What is a unique key?
8. What is a foreign key?
9. What is a join?
10. What are the types of join and explain each?
11. What is normalization?
12. What is Denormalization.
13. What are all the different normalizations?
14. What is a View?
15. What is an Index?
16. What are all the different types of indexes?
17. What is a Cursor?
18. What is a relationship and what are they?
19. What is a query?
20. What is subquery?
21. What are the types of subquery?
22. What is a stored procedure?
23. What is a trigger?
24. What is the difference between DELETE and TRUNCATE commands?
25. What are local and global variables and their differences?
26. What is a constraint?
27. What is data Integrity?
28. What is Auto Increment?
29. What is the difference between Cluster and Non-Cluster Index?
30. What is Datawarehouse?
31. What is Self-Join?
32. What is Cross-Join?
33. What is user defined functions?
34. What are all types of user defined functions?
35. What is collation?
36. What are all different types of collation sensitivity?
37. Advantages and Disadvantages of Stored Procedure?
38. What is Online Transaction Processing (OLTP)?
39. What is CLAUSE?
40. What is recursive stored procedure?
41. What is Union, minus and Interact commands?
42. What is an ALIAS command?
43. What is the difference between TRUNCATE and DROP statements?
44. What are aggregate and scalar functions?
45. How can you create an empty table from an existing table?
46. How to fetch common records from two tables?
47. How to fetch alternate records from a table?
48. How to select unique records from a table?
49. What is the command used to fetch first 5 characters of the string?
50. Which operator is used in query for pattern matching?
*/Example 2: sql interview queries and answers
CREATE DATABASE ORG;
SHOW DATABASES;
USE ORG;
CREATE TABLE Worker (
WORKER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
FIRST_NAME CHAR(25),
LAST_NAME CHAR(25),
SALARY INT(15),
JOINING_DATE DATETIME,
DEPARTMENT CHAR(25)
);
INSERT INTO Worker
(WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES
(001, 'Monika', 'Arora', 100000, '14-02-20 09.00.00', 'HR'),
(002, 'Niharika', 'Verma', 80000, '14-06-11 09.00.00', 'Admin'),
(003, 'Vishal', 'Singhal', 300000, '14-02-20 09.00.00', 'HR'),
(004, 'Amitabh', 'Singh', 500000, '14-02-20 09.00.00', 'Admin'),
(005, 'Vivek', 'Bhati', 500000, '14-06-11 09.00.00', 'Admin'),
(006, 'Vipul', 'Diwan', 200000, '14-06-11 09.00.00', 'Account'),
(007, 'Satish', 'Kumar', 75000, '14-01-20 09.00.00', 'Account'),
(008, 'Geetika', 'Chauhan', 90000, '14-04-11 09.00.00', 'Admin');
CREATE TABLE Bonus (
WORKER_REF_ID INT,
BONUS_AMOUNT INT(10),
BONUS_DATE DATETIME,
FOREIGN KEY (WORKER_REF_ID)
REFERENCES Worker(WORKER_ID)
ON DELETE CASCADE
);
INSERT INTO Bonus
(WORKER_REF_ID, BONUS_AMOUNT, BONUS_DATE) VALUES
(001, 5000, '16-02-20'),
(002, 3000, '16-06-11'),
(003, 4000, '16-02-20'),
(001, 4500, '16-02-20'),
(002, 3500, '16-06-11');Example 3: sql interview questions
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0Example 4: sql interview questions
/*SQL query to fetch duplicate records from a table .
Ans. In order to find duplicate records from the table, we can use GROUP BY on all the fields and then use the HAVING clause to return only those fields whose count is greater than 1 i.e. the rows having duplicate records.
*/
SELECT FullName, ManagerId, DateOfJoining, City, COUNT(*) FROM EmployeeDetails GROUP BY FullName, ManagerId, DateOfJoining, City HAVING COUNT(*) > 1;