Basic SQL Statements Flashcards
Week 8 Content
Name some RDBMS( Relational Database Management Services)
MySQL
MS Access
SQL Server
Oracle
Write sample code creating a table called newTable from an existing table called oldTable and explain what’s happening?
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
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.
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
What are the limitations that come with views
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.
How does the DEFAULT constraint work?
Automatically assigns a value to a column if no data was given
What is a candidate key?
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.
How do you drop a column called address from the Customer table using ALTER TABLE?
Using the DROP COLUMN statement followed by the column name.
ALTER TABLE Customers
DROP COLUMN address
What is the difference between a base table and a derived table in SQL?
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).
How can you add a new column to an existing table using ALTER TABLE?
By using the ADD keyword along with the column name, data type, and constraints.
ALTER TABLE CUSTOMERS
ADD age INT(3) NOT NULL;
How can you change the datatype of an existing column to VARCHAR(100) using ALTER TABLE?
ALTER TABLE CUSTOMERS
ALTER COLUMN customerName VARCHAR(100)
How can you drop a constraint using ALTER TABLE?
ALTER TABLE CUSTOMERS
DROP CONSTRAINT customer_PK
What can Alter table be used for?
- Adding a new column to an existing table
- Alter existing columns
- Add/Delete/Alter constraints using ADD, DROP, ALTER CONSTRAINT Keywords
When you add a new column to an existing table what value will the rows have for that column?
NULL
What does the Check constraint do? and provide syntax
Allows only certain values for this column/columns
example:
CONSTRAINT checkage CHECK (age >= 18)
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
True
What does the Truncate command do? and provide code that truncates from a table called customers
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;
Provide code to insert a row into a table called person. Columns are name, birthday, school. Use your own info
INSERT INTO person(name, birthday, school) VALUES (“Tenzing”, “March 1st”, “Sheridan”);
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’)
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.
Assuming you have 2 existing tables with the same structure, table1 and table2. How would you insert all the data from table1 into table2?
INSERT INTO table2
select * from table1
Given a table called Customers, how would you delete rows where the name is Brian?
DELETE FROM Customers
WHERE name = ‘Brian’;
How do you delete all rows from a table called Person?
DELETE FROM Person
or
TRUNCATE TABLE Person
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
a. UPDATE Customer
SET name = ‘Peter’
WHERE id = 5;
b. UPDATE Product
SET price = 10
WHERE name = ‘Basketball’
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?
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
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
create view person_view(pName, pAge, pSchool) as
select name, age, school from Person
drop view person_view
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)
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
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)
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;
What are Inline Views?
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)
Display all data from a derived table that gets all the employee data of employees with a salary greater than 50000
SELECT * FROM (SELECT * FROM EMPLOYEES
WHERE SALARY > 50000)
Using CTE create an inline view and display it
WITH CTE_Employee (employee_id, salary) AS
( Select department_id, AVG(Salary)
Group by department_id)
SELECT * from CTE_Employee
Create a CTE called EmployeeSalaries using the name, and salary columns from the employees table
WITH EmployeeSalaries AS (SELECT name, salary from Employees
Select * from EmployeeSalaries
