Upgrade to remove ads
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 termsSQL Interview Questions
90 terms
pjp797
SQL
78 terms
e1m2c4
SQL Interview
220 terms
ki7hy
Sets found in the same folderWeek 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 creatorSDLC 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
Recommended textbook solutions
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 setsChapter 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