SQL 3

Question 1

Querying the EMP table, Use the IN clause to return all employees who started (STARTDATE) in 2017 and 2019.
Only want to see NAME and DEPT columns.

Answer 1

SELECT name, dept
FROM EMP
WHERE startdate IN (2017, 2019);

Question 2

The IN Clause reduces the amount of text in a query by replacing what clause?

Answer 2

The OR Clause

Question 3

Using the IN clause rewrite this query reducing the amount of SQL used

SELECT *
FROM EMP
WHERE firstname = ‘Peter’
OR Firstname = ‘Steven’
OR Firstname = ‘Paul’
OR Firstname = ‘Alan’
OR Firstname = ‘Tom’;

Answer 3

SELECT *
FROM EMP
WHERE Firstname IN (‘Peter’, ‘Steven’, ‘Paul’, ‘Alan’, ‘Tom’);

Question 4

What would we do differently when writing an IN clause containing text rather than numbers? For example names rather than years.

Answer 4

We have to contain the text in quotation marks

Question 5

Do we ever wrap text in double quotes “ ” in SQL?

Answer 5

No, we only use single quotes ‘ ’

Question 6

How would we modify the IN clause to return all columns from a table called Staff that does not show the Accounts or Personnel dept? (from the dept column)

Answer 6

SELECT *
FROM Staff
WHERE dept NOT IN (‘Accounts’, ‘Personnel’);

Question 7

Can you name another data type apart from Text that we have to enclose in quotation marks?

Answer 7

Date

Question 8

What is the BETWEEN Operator used for?

Answer 8

The BETWEEN operator returns a range of data. This can be numbers, dates, text etc…

Question 9

Write a query that returns all employees that receive a salary between 25000 and 40000

All columns to be returned
EMP Table

Answer 9

SELECT *
FROM EMP
WHERE Sal BETWEEN 25000 AND 40000;

Question 10

Is the BETWEEN Operator Inclusive or non inclusive?

Answer 10

Inclusive

Eg BETWEEN 20 AND 40 would return matches for 20 and 40 as well as everything in between.

Question 11

Write a query that will return all Department Numbers DEPTNO not between 20 and 30

All Columns to be returned
Table name DEPT

Answer 11

SELECT *
FROM Dept
Where DEPTNO NOT BETWEEN 20 AND 30;

Question 12

Write query that would return all columns from the Dept table that have no data in the DEPTNO column

Answer 12

SELECT *
FROM Dept
WHERE DeptNo IS NULL;

Question 13

Write a query that returns those employees that don’t make any commission and have a salary greater than 1100 but less than 5000. Exclude those employees that have a salary equal to 3000
In this table the employees that don’t receive commission are either don’t have anything written in the comm column or have a 0 written in the comm column. Your query has to accommodate this

Answer 13

SELECT *
FROM EMP
WHERE ( COMM IS NULL OR COMM = 0 )
AND SAL > 1100 
AND SAL < 5000
AND SAL != 3000;

Question 14

When writing the not equals to operator we use !=. What is an alternative way to write not equals to?

Answer 14

Less than greater than < >

Question 15

The presence of parenthesis causes the conditions within them to be……?

Answer 15

Evaluated together

Question 16

What do we do with ANDs and ORs?

Answer 16

Group them together. Maybe even encapsulate them in parenthesis in order to ensure they are evaluated together.

Question 17

If you have a lot of AND conditions in SQL how should you write them?

Answer 17

They should all be combined together

Eg
CORRECT
AND SAL > 1100 AND SAL < 5000
AND SAL != 3000
OR COMM = 0;

NOT CORRECT
AND SAL > 1100 AND SAL < 5000
OR COMM = 0
AND SAL != 3000;