SQL 2

Question 1

What do we use the ; (semi colon) for in SQL?

Answer 1

The ; (Semi colon) is used to terminate a statement.

Question 2

What statement would return all records from a Table called ‘Emp’ where the surname is Jones. We Want all columns returned.

Answer 2

SELECT *
FROM Emp
WHERE surname = ‘Jones’;

Question 3

We want to return surname Jones and JobTitle is Musician. What statement will do this?

Table name is Emp
All Columns to be Returned

Answer 3

SELECT *
FROM Emp
WHERE surname = ‘Jones’
AND JobTitle = ‘Musician’;

Question 4

What order would these words appear in an SQL statement? WHERE, FROM, AND SELECT

Answer 4

SELECT
FROM
WHERE
AND

Question 5

From a table called EMP we want to return only the firstname, secondname, Sal and dept columns where surname is Jones and Sal is more than 60000

Answer 5

SELECT Firstname, secondname, sal, dept
FROM EMP
WHERE secondname = ‘Jones’
AND Sal > 60000;

Question 6

STRINGS are wrapped in…,,,,,,?

Answer 6

Quotation Marks

Question 7

Write a query that will return all employees that have a commission (Comm) greater than their Salary (Sal).

Tablename is EMP
All Columns to be returned

Answer 7

SELECT *
FROM Emp
WHERE comm > sal;

Question 8

Write a query that returns all employees (Job) that are NOT managers, have a salary greater than 25000 and Deptno is 20.

Tablename is EMP
All rows to be returned

Answer 8

SELECT *
FROM Emp
WHERE job != 'Manager'
AND sal > 25000
AND deptno = 20;

Question 9

Write a query that returns all employees whose job title is either Clerk or salesman.

Tablename is Emp
All Columns to be returned

Answer 9

SELECT *
FROM Emp
WHERE JOB = ‘Clerk’
OR Job = ‘Salesman’;

Question 10

Write a query that returns all employees that are not managers or Salesman and earn a salary greater than or equal to 25000

Tablename is Emp
We only want to see the name column

Answer 10

SELECT Name
FROM Emp
WHERE Job != 'Manager'
AND Job != 'Salesman'
AND Salary >= 25000;

Question 11

Check out the below task and the solution. Why do we use ‘AND Job != ‘Salesman’ rather than ‘OR Job != ‘Salesman’?

Write a query that returns all employees that are not managers or Salesman and earn a salary greater than or equal to 25000

Tablename is Emp
We only want to see the name column

SELECT Name
FROM Emp
WHERE Job != 'Manager'
AND Job != 'Salesman'
AND Salary >= 25000;

Answer 11

We use AND rather than OR because by using OR we are cancelling out both clauses

not equal to manager
OR not equal to Salesman

Means that the result set will display all managers and all salesmen.

SQL can parse the command but it will not return what we want. Therefore AND is the only solution. This will cancel out both managers and salesmen.

Question 12

What does Parse mean?

Answer 12

To break something down into parts in order to understand each element.

Question 13

What is the difference between Data Definition language (DDL) and Data manipulation Language (DML)?

Answer 13

DDL (Data Definition Language) is used to create and modify the structure of a database.

DML (Data Manipulation Language) is used to access, modify or retrieve the data from the database.