cscd 327 exam 1

(True or False) Database application programmers mainly use DDL to access databases

False. Manipulation idk

is a Parent/child relationship a must?

No. Any columns can serve as matching columns, provided they have comparable data types.

Answers to problem 32

False: a, e, f True: b,c,d

(True or false) SQL stands for Standard Query Lang.

false. Structured

The degree of a table is the number of columns in the table

True

what operation is this ∏?

Project Operation

What is attribute?

Attribute is the columns in a table

What is primary key?

Attribute that whose values never really change and have a unique identifier

what does (IS NULL) mean?

Checks whether a column has a NULL value.

what does (IN) do?

Checks whether the value of an expression matches one of a set of values; select name, manager from salesreps where manager in (104, 106);

how to add comments to sql

--

What 2 conditions must be true in order for r ∪ s to be valid.

1. r, s must have the same arity/degree (same number of attributes) 2. The attribute domains must be compatible (example: 2nd column of r deals with the same type of values as does the 2nd column of s)

identify the three syntax errors in the following statement: Create table EMPLOYEE( ID integer; NAME varchar, DOB date, Primary key(ID), Primary key(name) );

1. semi colon after integer 2. character amount is needed in varchar. ex: varchar(20) 3. can only have one primary key

what is domain?

A set of values for each attribute

what is tuple?

A tuple is the rows in a table

Add one column price that stores values of up to three digits to the left of the decimal point and two to the right. This column should have a default value of 9.99.

ALTER TABLE products add price NUMERIC(3,2) DEFAULT 9.99

Add one column time_added to denote the date and time when a product is added to the database.

ALTER TABLE products add time_added DATETIME

Use appropriate DDL to modify the users table created in Question 3 so that the first_name column cannot store NULL values and can only store a maximum of 20 characters.

ALTER TABLE usersMODIFY first_name Varchar(20) NOT NULL

answer to 23

CREATE TABLE users (user_id int NOT NULL AUTO_INCREMENT, email_address Varchar(100), first_name Varchar(45), last_name Varchar(45), primary key(user_id));

When can one use a natural joint

Can only be used when the two matching columns have the exactly same name in both tables.

in what order is the query processed?

Form the product of the tables named in the FROM clause. - Apply matching-column condition specified by ON clause. - Apply select condition specified by WHERE clause. - Keep the columns listed in SELECT clause. - Remove duplicates if SELECT DISTINCT is specified. - Sort the query results based on ORDER BY clause.

how to add data into a table in sql

INSERT INTO table_name (column1, column2, column3, ...)VALUES (value1, value2, value3, ...);

what is foreign key?

May have an attribute that links to another relation (Often primary keys)

what is cardinality?

Number of Tuples the relation contains

what is degree?

Number of attributes the relation contains

Find the instructor ids of all instructors in the Physics department, along with the course_id of all courses they have taught

Query Solution 1 ∏instructor.ID,course_id (σdept_name="Physics" ( σ instructor.ID=teaches.ID (instructor x teaches))) Query Solution 2 ∏instructor.ID,course_id (σinstructor.ID=teaches.ID ( σ dept_name="Physics" (instructor) x teaches))

What is relation?

Relation is set of tuples and Attributes

Find all the employees in department 10, along with any employees who earn a commission (i.e., comm isn't null), along with any employees in department 20 whose salary are at most $2000.

SELECT * FROM EMP WHERE DEPTNO = 10 OR COMM is not NULL OR (DEPTNO = 20 and SAL <= 2000);

List the name of each salesperson and the city and region where they work. What is the parent and child?

SELECT EMPL_NUM, NAME, CITY, REGION FROM SALESREPS, OFFICES WHERE REP_OFFICE = OFFICE; SALESPERSONS is the child; OFFICES is the parent.

List the name of each salesperson and the city and region where they work. Replace where with join

SELECT EMPL_NUM, NAME, CITY, REGION FROM SALESREPS, OFFICES WHERE REP_OFFICE = OFFICE; SELECT EMPL_NUM, NAME, CITY, REGION FROM SALESREPS JOIN OFFICES ON REP_OFFICE = OFFICE;

Return employee names and jobs from table EMP and sort by the last THREE characters in the job field. The result set should look like the following:

SELECT ENAME, JOB FROM EMP ORDER BY SUBSTRING(JOB, -3) ASC;

Find all the employees in departments 10 and 20, and return only those that have either an "I" somewhere in their name or a job title ending with "ER".

SELECT ENAME, JOB FROM EMP WHERE DEPTNO = 10 OR DEPTNO = 20 group by ENAME, JOB having ENAME like '%I%' OR JOB like '%ER'

Sometimes you want to perform IF-ELSE operations on values in your SELECT statement. For example, you would like to produce a result set such that, if an employee is paid $2000 or less, a message of "UNDERPAID" is returned, if an employee is paid $4000 or more, a message of "OVERPAID" is returned, if they make somewhere in between, then "OK" is returned. The result set should look like this:

SELECT ENAME, SAL, CASE WHEN SAL <= 2000 THEN 'UNDERPAID' WHEN SAL >= 4000 THEN 'OVERPAID' ELSE 'OK' END AS STATUS from EMP

List all authors whose last name contains the letter pattern "IN". Put the results in order of last name, then first name. List each author's last name and first name.

SELECT Lname, Fname FROM AUTHOR group by Lname, Fname having lname like '%IN%'

List the cities of offices with a target over $600,000 and their manager information (NAME and TITLE). Replace where with join

SELECT OFFICE, CITY, NAME, TITLE FROM OFFICES, SALESREPS WHERE MGR = EMPL_NUM AND TARGET > 600000.00; SELECT OFFICE, CITY, NAME, TITLE FROM OFFICES JOIN SALESREPS ON MGR = EMPL_NUM WHERE TARGET > 600000.00;

List the city of each office and the names and titles of their managers.

SELECT OFFICE, CITY, NAME, TITLE FROM OFFICES, SALESREPS WHERE MGR = EMPL_NUM; SALESPERSONS is the parent; OFFICES is the child.

List the city of each office and the names and titles of their managers. Replace where with join

SELECT OFFICE, CITY, NAME, TITLE FROM OFFICES, SALESREPS WHERE MGR = EMPL_NUM; SELECT OFFICE, CITY, NAME, TITLE FROM OFFICES JOIN SALESREPS ON MGR = EMPL_NUM;

List orders having amount over $25,000, also include the name of the salesreps who took the order and the company name of the customer who placed it. replace where with join

SELECT ORDER_NUM, AMOUNT, COMPANY, NAME FROM ORDERS, CUSTOMERS, SALESREPS WHERE CUST = CUST_NUM AND REP = EMPL_NUM AND AMOUNT > 25000.00; SELECT ORDER_NUM, AMOUNT, COMPANY, NAME FROM ORDERS JOIN CUSTOMERS ON CUST = CUST_NUM JOIN SALESREPS ON REP = EMPL_NUM WHERE AMOUNT > 25000.00;

List all the orders, showing order number, amounts and product descriptions. use a natural joint

SELECT ORDER_NUM, AMOUNT, DESCRIPTION FROM ORDERS NATURAL JOIN PRODUCTS;

List the ENAME and JOB of employees assigned to department number 10.

SELECT ename, job FROM EMP WHERE deptno = 10

Find all the books that are NOT in the Fitness category. List each book title and category.

SELECT TITLE, CATEGORY FROM BOOKS WHERE CATEGORY != 'fitness'

Return one column from the Customers table named full_name that joins the last_name and first_name columns. Format this column with the last name, a comma, a space, and the first name like this: Doe, John. Sort the result set by last name in ascending order and return only the customers whose last name begins with letters from M to Z.

SELECT concat(last_name, ', ', first_name) AS full_name FROM customers WHERE last_name >= 'M' ORDER BY last_name ASC

Find all the customers who live in Georgia or New Jersey. Put the results in ascending order by last name. List each customer's customer number, last name, and state.

SELECT customer_num, lastName, state FROM CUSTOMERS WHERE state = 'NJ' or state = 'GA' ORDER BY lastName ASC

Use a search pattern to find any book title with "A" for the second letter and "N" for the fourth letter. List each book's ISBN and title. Sort the list by title in descending order.

SELECT isbn, title FROM BOOKS having title like '_A_N%' ORDER BY title desc

what operation is this σ p(r)?

Select operation

What operation is this r - s?

Set difference operation

Find the largest salary in the university

Step 1: find instructor salaries that are less than some other instructor salary (i.e. not maximum) using a copy of instructor under a new name d ∏instructor.salary (σ instructor.salary < d,salary (instructor x ρd (instructor))) Step 2: Find the largest salary ∏salary (instructor) - ∏instructor.salary (σ instructor.salary < d,salary (instructor x ρd (instructor)))

Like condition: what does _ and % mean?

_ means a single character % could be zero or more characters

(true or false) consider the following relation R = (A,B,C) -> turn card to see photo that is needed to answer the following questions

a) A is a super key of relation b) AB is a super key of relation R c) ABC is a super key of relation R d) AB is a candidate key of relation R e) AC is super key of relation R f) AC is candidate key of relation R

what is candidate key?

it is a minimal superkey. Any set of columns must have unique values in each row.

what is ρ x (E)?

its the rename operation

what does select DISTINCT means?

remove duplicates

Select * ?

select all columns

what does select ALL mean?

select all rows

Find the names of all instructors in the Comp. Sci. department together with the course titles of all the courses that the instructors teach

∏ name, title (σ dept_name="Comp. Sci." (instructor teaches course))

Ex: To eliminate everything except the dept_name attribute of instructor?

∏ID, name, salary (instructor)

Example: to find all courses taught in the Fall 2009 semester, but not in the Spring 2010 semester

∏course_id (σ semester="Fall" Λ year=2009 (section)) − ∏course_id (σ semester="Spring" Λ year=2010 (section))

Example: to find all courses taught in the Fall 2009 semester and also in the Spring 2010 semester

∏course_id (σ semester="Fall" Λ year=2009 (section)) ∩ ∏course_id (σ semester="Spring" Λ year=2010 (section))

Example: to find all courses taught in the Fall 2009 semester, or in the Spring 2010 semester, or in both

∏course_id (σ semester="Fall" Λ year=2009 (section)) ∪ ∏course_id (σ semester="Spring" Λ year=2010 (section))