Sofia Relational Databases Unit 1.2: SIngle Table Construction

C ****** The primary key constraint applies to two other constraints: the NOT NULL constraint and the UNIQUE constraint. It ensures that all values in a column both exist and are unique.

Which of these constraints ensures that all values in a column are BOTH different and not empty? a.)FOREIGN KEY b.)UNIQUE c.)PRIMARY KEY d.)NOT NULL

B ******** Common mistakes when modifying the data type of a column include: not casting the variables if needed; not including the table and column names to change; the column being a primary or foreign key; and failing to consider the error messages that may occur when converting the existing data in the table.

Given the invoice table and the data that it contains, and assuming that you have the rights to modify the table, which of the following ALTER TABLE statements would work without errors? a.)ALTER TABLE invoice ALTER COLUMN billing_state int; b.)ALTER TABLE invoice ALTER COLUMN total TYPE VARCHAR (100); c.)ALTER TABLE customer ALTER COLUMN billing_city VARCHAR (100); d.) ALTER TABLE invoice ALTER COLUMN invoice_date TYPE INT

D

Given the tables provided, which of the following DROP TABLE series of statements would correctly remove the tables without causing an error? a.)DROP TABLE album;DROP TABLE artist;DROP TABLE playlist; b.)DROP TABLE playlist_track;DROP TABLE artist;DROP TABLE playlist; c.)DROP TABLE track;DROP TABLE playlist_track;DROP TABLE playlist; d.)DROP TABLE playlist_track;DROP TABLE playlist;DROP TABLE invoice_line;

c

Given the tables provided, which of the following DROP TABLE series of statements would correctly remove the tables without causing an error? a.)DROP TABLE customer;DROP TABLE invoice;DROP TABLE invoice_line; b.)DROP TABLE invoice;DROP TABLE invoice_line;DROP TABLE customer; c.)DROP TABLE invoice_line;DROP TABLE invoice;DROP TABLE customer; d.)DROP TABLE track;DROP TABLE playlist_track;DROP TABLE artist;

B

Identify the correctly constructed ALTER TABLE statement that adds the column address as a VARCHAR (100) to the customer table. a.) ALTER TABLE customer ADD address; b.) ALTER TABLE customer ADD address VARCHAR (100); c.) ALTER TABLE customer ADD home_address VARCHAR (100); d.)ALTER TABLE customer DROP address VARCHAR (100);

B

Identify the correctly constructed ALTER TABLE statement that adds the column age as an int to the user table. a.)ALTER TABLE user DROP age int; b.) ALTER TABLE user ADD age int; c.)ALTER TABLE user ADD age; d.) ALTER TABLE user ADD age_now int;

D

Identify the correctly constructed ALTER TABLE statement that adds the column password as a VARCHAR (100) to the user table. a.)ALTER TABLE user DROP password VARCHAR (100); b.) ALTER TABLE user ADD password; c.) ALTER TABLE user ADD pass VARCHAR (100); d.) ALTER TABLE user ADD password VARCHAR (100);

A

Identify the correctly constructed ALTER TABLE statement to add a UNIQUE constraint to the column customer_number with the constraint name customer_number_unique on the table called 'customer'. a.)ALTER TABLE customer ADD CONSTRAINT customer_number_unique UNIQUE (customer_number); b.)ALTER TABLE customer ADD CONSTRAINT customer_number UNIQUE (customer_number_unique); c.)ALTER TABLE customer ADD UNIQUE customer_number CONSTRAINT (customer_number_unique); d.)ALTER TABLE customer ADD CONSTRAINT customer_number UNIQUE (customer_number);

a ****** a.)Correct. This code will cause an error because the column is a foreign key

Given the customer table and the data that it contains, and assuming that you have the rights to modify the table, which of the following ALTER TABLE statements would create an error? a.) ALTER TABLE customerALTER COLUMN support_rep_id TYPE VARCHAR (100); b.) ALTER TABLE customerALTER COLUMN city TYPE VARCHAR (100); c.) ALTER TABLE customerALTER COLUMN state TYPE VARCHAR (100); d.) ALTER TABLE customerALTER COLUMN postal_code TYPE VARCHAR (100);

B

Given the customer table and the data that it contains, and assuming that you have the rights to modify the table, which of the following ALTER TABLE statements would create an error? a.)ALTER TABLE customer ALTER COLUMN postal_code TYPE text; b.)ALTER TABLE customer ALTER COLUMN city TYPE INT; c.)ALTER TABLE customer ALTER COLUMN postal_code TYPE VARCHAR (100); d.)ALTER TABLE customer ALTER COLUMN state TYPE VARCHAR (100);

C

Given the employee table and the data that it contains, and assuming that you have the rights to modify the table, which of the following ALTER TABLE statements would work without errors? a.) ALTER TABLE employee ALTER COLUMN email TYPE INT; b.)ALTER TABLE employee ALTER COLUMN email VARCHAR (100); c.)ALTER TABLE employee ALTER COLUMN postal_code TYPE TEXT; d.)ALTER TABLE customer ALTER COLUMN city TYPE INT;

D

Which of these constraints can be used to verify that the data is in a specific range? a.)PRIMARY KEY b.)UNIQUE c.)FOREIGN KEY d.)CHECK

D

Which of these constraints ensures that the column cannot be empty? a.)FOREIGN KEY b.)NOT EMPTY c.)UNIQUE d.)NOT NULL

B

Select the correctly constructed CHECK constraint to validate the total column of type int, to ensure that values placed into it are greater than or equal to 0. a.)CHECK (total > 0) b.)CHECK (total >= 0) c.)CHECK (total > -1) d.)CHECK (total column > 0)

A

The following CREATE TABLE statement creates a table called 'company' that consists of the company_id as the primary key, the company_name, and the address.CREATE TABLE company(company_id INT PRIMARY KEYcompany_name VARCHAR (100),address VARCHAR (100)); Identify the line of code that would generate an error in this CREATE TABLE statement. a.)2 b.)3 c.)4 d.)1

C

The following CREATE TABLE statement creates a table called 'user' that consists of the user_id as the primary key, the username, and the password.CREATE TABLE useruser_id int PRIMARY KEY,username VARCHAR (50),password VARCHAR (50)); Identify the line of code that would generate an error in this CREATE TABLE statement. a.)3 b.)4 c.)1 d.)2

b

The following CREATE TABLE statement should create a table called 'company' that consists of the company_id as the primary key that is auto-incremented, the company_name, and the address.CREATE TABLE company(company_id serial PRIMARY KEY,company_name int,address VARCHAR (100)); Identify the incorrect line of code in this CREATE TABLE statement.

A

The following CREATE TABLE statement should create a table called 'employee' that consists of the employee_id as the primary key that is auto-incremented, the first_name, and the last_name.CREATE TABLE employeeemployee_id serial PRIMARY KEY,first_name VARCHAR (50),last_name VARCHAR (50)); Identify the line of code that would either generate a syntax, logical, or requirements error in this CREATE TABLE statement. a.)1 b.)2 c.)4 d.)3

B

The following CREATE TABLE statement should create a table called 'users' that consists of the user_id as the primary key that is auto-incremented, the username, and the password.CREATE TABLE users(user_id int PRIMARY KEY,username VARCHAR (50),password VARCHAR (50)); Identify the incorrect line of code in this CREATE TABLE statement.