SQL query to fetch all the employees who are not working on any project

Upgrade to remove ads

Table of Contents Show

Which SQL query will SELECT employee details whose department is not present in department table?
How will you write an SQL query to fetch the count of the employee working in project Q1?
How do I fetch all records in SQL?
Which of the following is an SQL query to fetch all employees from EmployeeDetails who have a salary record in EmployeeSalary?

Only CA$44.99/year

Science
Computer Science

Flashcards
Learn
Test
Match

Flashcards
Learn
Test
Match

Terms in this set (34)

What are the 5 sub-languages of SQL?

DDL: Data Definition Language, create/alter tables, DEFINE structure of a database.
DML: Data Manipulation language, creating/ manipulating data within a table. CRUD operations.
DQL: Data Query Language, SELECT is argued to be part of this sublanguage.
DCL: Data Control Language, regulate access to the data by imposing restrictions.
TCL: Transaction Control Language, used to control database transactions (a single unit of work).

What commands are associated with DDL?

CREATE, ALTER, TRUNCATE, DROP.

What commands are associated with DML?

INSERT, SELECT, UPDATE, DELETE.

What commands are associated with DQL?

SELECT

What commands are associated with TCL?

COMMIT, ROLLBACK, SAVEPOINT.

What commands are associated with DCL?

GRANT and REVOKE.

What relationships can you have between tables? (multiplicity)

1 to 1, 1 to many (many to 1), and Many to Many.

What SQL constraints are there?

PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.

What is the difference between LEFT/RIGHT, INNER, and OUTER joins?

INNER: returns records that have matching values in both tables.
LEFT: returns all records from the left table, and the matched records from the right table.
RIGHT, opposite of LEFT.
FULL/ OUTER: Returns all records when there is a match in either left or right table.

What are scalar v. aggregate functions?

Aggregate: function that acts as many rows of data and gives a single value as output.
Example function:
SELECT AVG(student_age) FROM students;

Scalar: function that acts on individual rows of data.
Example function:
SELECT student_first_name, LENGTH(student_first_name), student_last_name, LENGTH(student_last_name) FROM students;

What is the purpose of the GROUP BY clause? What type of function (scalar? aggregate?) do we use with GROUP BY?

To create different groups based on the column having the same value, finding the average between two groups instead of finding the entire average. AVG is used with GROUP BY.

What is the difference between WHERE and HAVING?

WHERE filters out individual rows of data based on a condition not being met, while HAVING filters out the conditions that already have grouped together data.

How does ORDER BY order the data from a query by default (ascending? descending?) ? How do we have it do the opposite?

ORDER BY is ascending by default, just add DESC at the end, for example. EX: ORDER BY grade DESC;.

Write an SQL query to fetch records that are present in one table but not in another table.

SELECT * FROM EmployeeSalary
MINUS
SELECT * FROM ManagerSalary;

Fetch all the employees who are not working on any project.

SELECT EmpId
FROM EmployeeSalary
WHERE Project IS NULL;

Write an SQL query to fetch all the Employees details from EmployeeDetails table who joined in the Year 2020.

SELECT * FROM EmployeeDetails
WHERE DateOfJoining BETWEEN '2020/01/01'
AND '2020/12/31';

Write an SQL query to fetch all employee records from EmployeeDetails table who have a salary record in EmployeeSalary table.

SELECT * FROM EmployeeDetails E
WHERE EXISTS
(SELECT * FROM EmployeeSalary S
WHERE E.EmpId = S.EmpId);

Write an SQL query to fetch project-wise count of employees sorted by project's count in descending order.

SELECT Project, count(EmpId) EmpProjectCount
FROM EmployeeSalary
GROUP BY Project
ORDER BY EmpProjectCount DESC;

Write a query to fetch employee names and salary records. Display the employee details even if the salary record is not present for the employee.

SELECT E.FullName, S.Salary
FROM EmployeeDetails E
LEFT JOIN
EmployeeSalary S
ON E.EmpId = S.EmpId;

Write an SQL query to fetch all the Employees who are also managers from the EmployeeDetails table.

SELECT DISTINCT E.FullName
FROM EmployeeDetails E
INNER JOIN EmployeeDetails M
ON E.EmpID = M.ManagerID;

Write an SQL query to fetch duplicate records from EmployeeDetails (without considering the primary key - EmpId).

SELECT FullName, ManagerId, DateOfJoining, City, COUNT(*)
FROM EmployeeDetails
GROUP BY FullName, ManagerId, DateOfJoining, City
HAVING COUNT(*) > 1;

Write an SQL query to fetch only odd rows from the table.

SELECT * FROM EmployeeDetails
WHERE MOD (EmpId, 2) <> 0;

Write SQL query to find the 3rd highest salary from a table without using the TOP/limit keyword.

SELECT Salary
FROM EmployeeSalary Emp1
WHERE 2 = (
SELECT COUNT( DISTINCT ( Emp2.Salary ) )
FROM EmployeeSalary Emp2
WHERE Emp2.Salary > Emp1.Salary
)

Write a query to fetch the EmpFname from the EmployeeInfo table in upper case and use the ALIAS name as EmpName.

SELECT UPPER(EmpFname) AS EmpName FROM EmployeeInfo;

Write a query to fetch the number of employees working in the department 'HR'.

SELECT COUNT(*) FROM EmployeeInfo WHERE Department = 'HR';

Write a query to get the current date.

SELECT GETDATE();

Write a query to retrieve the first four characters of EmpLname from the EmployeeInfo table.

SELECT SUBSTRING(EmpLname, 1, 4) FROM EmployeeInfo;

Write a query to fetch only the place name(string before brackets) from the Address column of EmployeeInfo table.

SELECT SUBSTRING(Address, 1, CHARINDEX('(',Address)) FROM EmployeeInfo;

Write a query to create a new table which consists of data and structure copied from the other table.

SELECT * INTO NewTable FROM EmployeeInfo WHERE 1 = 0;

Write q query to find all the employees whose salary is between 50000 to 100000.

SELECT * FROM EmployeePosition WHERE Salary BETWEEN '50000' AND '100000';

Write a query to find the names of employees that begin with 'S'

SELECT * FROM EmployeeInfo WHERE EmpFname LIKE 'S%';

Write a query to fetch top N records.

SELECT TOP N * FROM EmployeePosition ORDER BY Salary DESC;

Write a query to retrieve the EmpFname and EmpLname in a single column as "FullName". The first name and the last name must be separated with space.

SELECT CONCAT(EmpFname, ' ', EmpLname) AS 'FullName' FROM EmployeeInfo;

What is the difference between Truncate and Drop?

Drop removes the whole database, table indexes, data, and more, thus permanently deleting all the data from the tables. While truncate removes all rows from the table, but it retains the structure of the table and columns, and is faster than the drop command.

Sets with similar terms

SQL Interview Questions

90 terms

pjp797

SQL

78 terms

e1m2c4

SQL Interview

220 terms

ki7hy

Sets found in the same folder

Week 8 part 2 Angular

32 terms

rafael_dantasPLUS

Week 8 part 1 Angular

40 terms

rafael_dantasPLUS

Week 9 Spring and Hibernate

63 terms

rafael_dantasPLUS

Week 10 Spring boot

13 terms

rafael_dantasPLUS

Other sets by this creator

SDLC interview Q/A

19 terms

rafael_dantasPLUS

DevOps and Rest Interview Q/A

10 terms

rafael_dantasPLUS

Testing concepts interview Q/A

37 terms

rafael_dantasPLUS

Java Interview Q/A

49 terms

rafael_dantasPLUS

Introduction to Algorithms

3rd EditionCharles E. Leiserson, Clifford Stein, Ronald L. Rivest, Thomas H. Cormen

726 solutions

Computer Organization and Design MIPS Edition: The Hardware/Software Interface

5th EditionDavid A. Patterson, John L. Hennessy

220 solutions

Information Technology Project Management: Providing Measurable Organizational Value

5th EditionJack T. Marchewka

346 solutions

Service Management: Operations, Strategy, and Information Technology

7th EditionJames Fitzsimmons, Mona Fitzsimmons

103 solutions

Other Quizlet sets

Chapter 17: Mood Disorders and Suicide ~ still wor…

20 terms

lovejackiexoxo

SMC- Test 1

294 terms

sarahmjohnson7PLUS

2e - Français - Les figures de style

39 terms

ascendansehiphopTEACHER

WH PMDD

5 terms

julia_frye7

Which SQL query will SELECT employee details whose department is not present in department table?

Here, we will see how to query to find the employees whose departments are not assigned by using the following SQL query as follows. SELECT* FROM employee WHERE emp_dept IS NULL; Output : In this table, all the employee records whose department is NULL value are obtained.

How will you write an SQL query to fetch the count of the employee working in project Q1?

How will you write an SQL Query to fetch the count of the employee working in project “Q1”?.

SELECT COUNT(*).

FROM Employee Salary..

WHERE Project = “Q1”;.

How do I fetch all records in SQL?

The syntax is: SELECT column1, column2 FROM table1, table2 WHERE column2='value';.

The SELECT clause specifies one or more columns to be retrieved; to specify multiple columns, use a comma and a space between column names. ... .

The FROM clause specifies one or more tables to be queried..

Which of the following is an SQL query to fetch all employees from EmployeeDetails who have a salary record in EmployeeSalary?

Ques.27. Write an SQL query to fetch all employee records from EmployeeDetails table who have a salary record in EmployeeSalary table. SELECT * FROM EmployeeDetails E WHERE EXISTS (SELECT * FROM EmployeeSalary S WHERE E.EmpId = S.EmpId);