Basic SQL Statements

Question 1

Name some RDBMS( Relational Database Management Services)

Answer 1

MySQL
MS Access
SQL Server
Oracle

Question 2

Write sample code creating a table called newTable from an existing table called oldTable and explain what’s happening?

Answer 2

select * into newTable from oldTable

this code creates a table called newTable and duplicates information from table into it.

NOTE: instead of oldTable which is a base table, a derived table can be used instead

Question 3

Given a table called Customers with these columns : customerName, address, DOB. Create the following views.

a. View called Name&Addr that only has customerName and address columns.

b. View called customerDOB that only has DOB

c. View called CustomersView that has all the columns.

Answer 3

a. create view Name&Addr as
select customerName, address from Customers

b. create view customerDOB as
select DOB from Customers

c. create view CustomersView as
select * from Customers

Question 4

What are the limitations that come with views

Answer 4

Defines a logical table from one or more tables or views.
There are limitations on updating data through a view.
Where views can be updated, those changes can be transferred to the underlying base tables
originally referenced to create the view.

Question 5

How does the DEFAULT constraint work?

Answer 5

Automatically assigns a value to a column if no data was given

Question 6

What is a candidate key?

Answer 6

A column or set of columns designated as UNIQUE, where only one candidate key can be a PRIMARY KEY. A table can have multiple candidate keys.

Question 7

How do you drop a column called address from the Customer table using ALTER TABLE?

Answer 7

Using the DROP COLUMN statement followed by the column name.

ALTER TABLE Customers
DROP COLUMN address

Question 8

What is the difference between a base table and a derived table in SQL?

Answer 8

A base table is a standalone table(Table made with a CREATE statement), while a derived table is created based on an existing table or query(Exists only in query).

Question 9

How can you add a new column to an existing table using ALTER TABLE?

Answer 9

By using the ADD keyword along with the column name, data type, and constraints.

ALTER TABLE CUSTOMERS
ADD age INT(3) NOT NULL;

Question 10

How can you change the datatype of an existing column to VARCHAR(100) using ALTER TABLE?

Answer 10

ALTER TABLE CUSTOMERS
ALTER COLUMN customerName VARCHAR(100)

Question 11

How can you drop a constraint using ALTER TABLE?

Answer 11

ALTER TABLE CUSTOMERS
DROP CONSTRAINT customer_PK

Question 12

What can Alter table be used for?

Answer 12

• Adding a new column to an existing table
• Alter existing columns
• Add/Delete/Alter constraints using ADD, DROP, ALTER CONSTRAINT Keywords

Question 13

When you add a new column to an existing table what value will the rows have for that column?

Answer 13

NULL

Question 14

What does the Check constraint do? and provide syntax

Answer 14

Allows only certain values for this column/columns

example:
CONSTRAINT checkage CHECK (age >= 18)

Question 15

True or False:

Some RDBMSs will not allow you to alter a table in a way that the current data in that table will
violate the new definitions

Answer 15

True

Question 16

What does the Truncate command do? and provide code that truncates from a table called customers

Answer 16

The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself. This data is permanently deleted.

TRUNCATE TABLE customers;

Question 17

Provide code to insert a row into a table called person. Columns are name, birthday, school. Use your own info

Answer 17

INSERT INTO person(name, birthday, school) VALUES (“Tenzing”, “March 1st”, “Sheridan”);

Question 18

Assuming Customer_T has columns CustomerID, customerName, age, and address. Assuming there aren’t any constraints what will happen with the code below?

INSERT INTO Customer_T (CustomerID,customerName) values (006, ‘ABCD’)

Answer 18

A row will be inserted with only CustomerID and customerName filled, the age and address fields will be left NULL. If there are any NOT NULL constraints then there would be an error.

Question 19

Assuming you have 2 existing tables with the same structure, table1 and table2. How would you insert all the data from table1 into table2?

Answer 19

INSERT INTO table2
select * from table1

Question 20

Given a table called Customers, how would you delete rows where the name is Brian?

Answer 20

DELETE FROM Customers
WHERE name = ‘Brian’;

Question 21

How do you delete all rows from a table called Person?

Answer 21

DELETE FROM Person
or
TRUNCATE TABLE Person

Question 22

How would up do the following problems:

a. Update Customer table where id is 5 and change their name to ‘Peter’

b. In the Product table set the price of ‘Basketball’ to 10

Answer 22

a. UPDATE Customer
SET name = ‘Peter’
WHERE id = 5;

b. UPDATE Product
SET price = 10
WHERE name = ‘Basketball’

Question 23

Create a view called test_view from the Employees, and job table that has columns Employee name from employee table, and job title from the job table. (jobId is the PK from job table).

And how to display all the contents of that view?

Answer 23

create view test_view as
select e.name, j.jobTitle from employee e join
job j
on e.jobId = j.jobId

select * from test_view

Question 24

Given a Person table, create a view called person_view that copies the name, age, and school columns and renames them to pName, pAge, pSchool.

And then drop that view

Answer 24

create view person_view(pName, pAge, pSchool) as
select name, age, school from Person

drop view person_view

Question 25

Create a view based on following select query

–Display emp’s name, department_name, difference between
max_salary, salary in positive numbers, show data of those
employees whose difference is more than 500

TABLES(COLUMNS):
EMPLOYEES(first_name, last_name, salary, department_id, job_id)
DEPARTMENTS(department_id, department_name)
JOBS(job_id, max_salary)

Answer 25

create view vw_emp_salary_data as
(select first_name+’ ‘+last_name as Name, DEPARTMENT_name,
abs(max_salary-salary) as Diff_Salary
from EMPLOYEES e join DEPARTMENTS d
on e.DEPARTMENT_ID=d.DEPARTMENT_ID
join JOBS j on e.JOB_ID=j.JOB_ID
where abs(max_salary-salary)>500);

Displaying the data from the view:
Select * from vw_emp_salary_data

Question 26

Create a view based on following select statement

Display department_name, avg salary in each department and
count of employees in each department

TABLES(COLUMNS):
EMPLOYEES(first_name, last_name, salary, department_id, job_id)
DEPARTMENTS(department_id, department_name)

Answer 26

create view vw_dept_avg_salary as
select department_name,avg(salary) as
‘Avg_salary’,
count(employee_id) as ‘Num_employees’
from EMPLOYEES e join DEPARTMENTS
d
on e.DEPARTMENT_ID=d.DEPARTMENT_ID
group by DEPARTMENT_NAME;

Question 27

What are Inline Views?

Answer 27

SQL includes the ability to specify a view from within a query.

There are two ways to do this:
* Using a derived table (DT) in the query’s FROM clause
* Using the WITH clause (Common Table Expression or CTE)

Question 28

Display all data from a derived table that gets all the employee data of employees with a salary greater than 50000

Answer 28

SELECT * FROM (SELECT * FROM EMPLOYEES
WHERE SALARY > 50000)

Question 29

Using CTE create an inline view and display it

Answer 29

WITH CTE_Employee (employee_id, salary) AS
( Select department_id, AVG(Salary)
Group by department_id)

SELECT * from CTE_Employee

Question 30

Create a CTE called EmployeeSalaries using the name, and salary columns from the employees table

Answer 30

WITH EmployeeSalaries AS (SELECT name, salary from Employees

Select * from EmployeeSalaries