SQL Flashcards
How do you fetch common records from two tables?
Use the INTERSECT statement.
Select studentID from student
INTERSECT
Select StudentID from Exam
BETWEEN statement exclusivity.
BETWEEN is Inclusive for Date ranges and Exclusive for letter ranges
How to fetch Even or Odd rows from a table?
Use MOD statement
Even Rows
where mod(rowno,2)=0
Odd Rows
where mod(rowno,2)=1
How to select unique records?
Use DISTINCT
Select DISTINCT EmailAddress From Blacklist
How to select substring of characters?
SUBSTRING function
Select SUBSTRING(StudentName,1,5) as studentname from student
Pattern Matching command and syntax?
LIKE operator
SELECT Name FROM Student LIKE ‘%brad%’
% - Matches zero or more characters.
_(Underscore) – Matching exactly one character.
What is the difference between TRUNCATE and DROP statements?
TRUNCATE removes all the rows from the table, and it cannot be rolled back. DROP command removes a table from the database and operation cannot be rolled back.
How can you create an empty table from an existing table?
SELECT INTO NewTable statement
Select * into studentcopy from student where 1=2
What is an ALIAS command?
ALIAS name can be given to a table or column. This alias name can be referred in WHERE clause to identify the table or column
Select st.StudentID, Ex.Result from student st, Exam as Ex where st.studentID = Ex. StudentID
How do you combine the result of two Select statements with the same schema structure without duplicates?
UNION Statement
How do you get a result set that include all rows of the Left table while excluding any that are present in the Right table?
MINUS Statement
What is CLAUSE?
SQL clause is defined to limit the result set by providing condition to the query.
ex. WHERE, GROUP BY, ORDER BY
What is collation?
Collation is defined as set of rules that determine how character data can be sorted and compared. This can be used to compare A and, other language characters and also depends on the width of the characters.
ASCII value can be used to compare these character data.
What will be the result of the query below? Explain your answer and provide a version that behaves correctly.
select case when null = null then ‘Yup’ else ‘Nope’ end as Result;
This query will actually yield “Nope”, seeming to imply that null is not equal to itself! The reason for this is that the proper way to compare a value to null in SQL is with the is operator, not with =.
To fix
select case when null is null then ‘Yup’ else ‘Nope’ end as Result;
LEFT JOIN
Statement that joins two tables on a condition, and keeps all rows of the LEFT table regardless of if the condition is met.
RIGHT JOIN
Statement that joins two tables on a condition, and keeps all rows of the RIGHT table regardless of if the condition is met.
INNER JOIN
Statement that joins two tables on a condition, and discards all rows for the tables that do not match the condition.
OUTER JOIN
Statement that joins all the rows of two tables and any tuples that meet the condition join together.
CROSS JOIN
Statement that joins all rows of LEFT table with all rows of the RIGHT table.
How do you find a specific character in a string in SQL
MySQL - LOCATE function
LOCATE (‘ ‘, FullName)
SQL Server - CHARINDEX
CHARINDEX(‘ ‘, FullName) - 1)
How to get a substring in SQL
MySQL - MID function SELECT MID(FullName, 0, LOCATE(' ',FullName)) FROM EmployeeDetails
SQL Server - SUBSTRING function SELECT SUBSTRING(FullName, 0, CHARINDEX(' ',FullName)) FROM EmployeeDetails
If you have an Employee Table with the Schema
{id, name, manager_id, date_joined}
How do you find employees who are also managers?
You would use a self-join on the Employee table.
SELECT DISTINCT E.FullName
FROM EmpDetails E
INNER JOIN EmpDetails M
ON E.EmpID = M.ManagerID;
How would you filter on a subquery where the result is not NULL
Using EXISTS
SELECT * FROM EmployeeDetails E WHERE EXISTS (SELECT * FROM EmployeeSalary S WHERE E.EmpId = S.EmpId);
How would you get duplicate records from a call?
Using GROUB BY + HAVING
SELECT EmpId, Project, Salary, COUNT()
FROM EmployeeSalary
GROUP BY EmpId, Project, Salary
HAVING COUNT() > 1;
Getting current Datetime?
MySQL - NOW()
SQL Server - getdate()
Oracle - SELECT SYSDATE FROM DUAL;
How to fetch the top N records?
MySQL - Limit
SELECT * FROM EmployeeSalary ORDER BY Salary DESC LIMIT N
SQLServer - SELECT TOP
SELECT TOP N * FROM EmployeeSalary ORDER BY Salary DESC
Oracle - ROWNUM
SELECT * FROM (SELECT * FROM EmployeeSalary ORDER BY Salary DESC)
WHERE ROWNUM <= 3;