Quiz 2 Intro to Database

Which one is NOT a domain type/data type in SQL?

• char

• varchar

• numeric

• class

Which command is CORRECT for creating a table?

• create table instructor ( ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2))

• insert into table instructor ( ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2))

• create database instructor ( ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2))

• alter table instructor ( ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2))

insert into instructor values ('10211', 'Smith', 'Biology', 66000);

What does this command?

• inserts a new value to instructor table

• updates instructor table with new value

• inserts a new value to instructor database

• changes the value of id 10211

I want to find the names of all instructors whose name INCLUDES the substring “dar”

Which one is CORRECT select query?

• select name from instructor where name = '%dar%'

• select name from instructor where name like 'dar%'

• select name from instructor where name like '%dar%'

• select name from instructor where name in '%dar%'

I want to list in alphabetic order the salary of all instructors with highest to smallest.

Which select query is CORRECT?

• select * from instructor order by salary asc

• select * from instructor order by salary desc

• select * from instructor order by salary

• select * from instructor where order by max

Find the total number of instructors who teach a course in the Spring 2018 semester

• select count (*) from teaches where semester = 'Spring' and year = 2018;

• select max(distinct ID) from teaches where semester = 'Spring' and year = 2018;

• select Id from teaches where semester = 'Spring' and year = 2018;

• select avg(*) from teaches where semester = 'Spring' and year = 2018;

select dept_name, avg (salary) as avg_salary from instructor

group by dept_name

having avg (salary) > 40000;

What does this query do?

• Find the department names and total salaries of all departments whose average salary is greater than 40000

• Find the department names and average salaries of all departments whose average salary is greater than 40000

• it gives error

• Find the salaries of all departments whose average salary is smaller than 40000

Delete all instructors whose salary is less than the average salary of instructors

• delete from instructor where salary < avg(salary)

• delete from instructor where salary < (select count (salary) from instructor);

• delete from instructor where salary < (select avg (salary) from instructor);

• delete avg (salary) from instructor where salary < avg(salary)

Find the names of all instructors in the Physics department (Relational Algebra)

• ∏Physics(σ instructor =“Physics” )

• σ dept_name=“Physics”

• σ dept_name=“Physics” (instructor)

• ∏name(σ dept_name =“Physics” (instructor))

Database applications are usually partitioned into some parts. According to this information, which type architecture is defined.

"The client machine acts as a front end and does not contain any direct database calls.

• The client end communicates with an application server, usually through a forms interface.

• The application server in turn communicates with a database system to access data."

• Two-tier architecture

• Three-tier architecture

• One-tier architecture

• Four-tier architecture