SQL Domains 5-7

Creating/Modifying Tables

Creating/Modifying Rows

Multiple Tables

100

You can create a table in a database using ___ statement. You can delete a table in a database using ___ statement.

a. INSERT TABLE, DELETE TABLE

b. CREATE TABLE, DROP TABLE

c. CREATE TABLE, ALTER TABLE

d. DROP TABLE, CREATE TABLE

b. CREATE TABLE, DROP TABLE

100

To insert a row into the table, we use the ___ statement with ___ and ___ that are entered into these columns.

a. column names, INSERT INTO, numbers

b. values, column names, numbers

c. INSERT INTO, column names, values

d. INSERT INTO, numbers, values

c. INSERT INTO, column names, values

100

Which type of SQL statement is used to update data in one table based on values from another table?

a. UPDATE JOIN

b. TABLE JOIN

c. DATA JOIN

d. INNER JOIN

a. UPDATE JOIN

200

A table is created using the following statement

Select all the correct answers.

CREATE TABLE Tbl1 (
    Id int,
    Name varchar(250) NOT NULL,
    Description NULL
)

a. Id can be NULL

b. Description can be NULL

c. Name can be NULL

a. Id can be NULL

b. Description can be NULL

200

Reorder the SQL statements in the way that you select only 'Alice' at the end of the sequence.

# Block 1 
SELECT Name FROM Customer

# Block 2 
INSERT INTO Customer (Name) VALUES ('Alice')

# Block 3
INSERT INTO Customer (Name) VALUES ('Bob')

# Block 4
DELETE FROM Customer

# Block 3 INSERT INTO Customer (Name) VALUES ('Bob')

# Block 4 DELETE FROM Customer
# Block 2 INSERT INTO Customer (Name) VALUES ('Alice')

# Block 1 SELECT Name FROM Customer

200

Construct the correct query.

# Block 1

ON p.BusinessEntityID = e.BusinessEntityID

# Block 2

FROM Person.Person p

# Block 3

JOIN HumanResources.Employee e

# Block 4

WHERE p.ModifiedDate > '2004-02-01'

# Block 5

SELECT p.FirstName, p.LastName

# Block 5

SELECT p.FirstName, p.LastName

# Block 2

FROM Person.Person p

# Block 3

JOIN HumanResources.Employee e

# Block 1

ON p.BusinessEntityID = e.BusinessEntityID

# Block 4

WHERE p.ModifiedDate > '2004-02-01'

300

You can identify a product using its serial number, and it is a whole number.

Also, product has a name that can be maximum 100 characters long.

CREATE TABLE Product (

SerialNumber ___,

Name ___

)

a. varchar PRIMARY KEY, int

b. int PRIMARY KEY, varchar(100)

c. int, varchar PRIMARY KEY

d. int PRIMARY KEY, varchar(100) PRIMARY KEY

b. int PRIMARY KEY, varchar(100)

300

See Modifying Rows 300 image

Tom and Bob are living in Lower Street 1

Cindy and Daphne are living in Lower Street 2

300

What are the different types of subqueries?

Select all answers that apply.

Multiple-row

Outer join

Left join

Correlated

Single-row

Correlated

Multiple-row

400

We want to create a table with the primary key as automatically generated whole number.

CREATE TABLE Product (

Id ___ ___,

Name varchar(100)

)

a. number, IDENTITY(1,1)

b. IDENTITY(1,1) PRIMARY KEY, number

c. IDENTITY(1,1), int

d. int, IDENTITY(1,1) PRIMARY KEY

int, IDENTITY(1,1) PRIMARY KEY

400

See Modifying Rows 400 image

# Block 3 INSERT INTO House (HouseId, AddedAt) VALUES (1, '2021-12-31')

# Block 2 INSERT INTO Tenant (HouseId, TenantName) VALUES (1, 'Bob'), (1, 'Alice')

# Block 1 UPDATE Tenant SET TenantName = 'Bob'

400

What will happen if we remove AS keyword after each table (instead "Tenant AS t", leave "Tenant t")?

SELECT *

FROM

Tenant AS t

JOIN House AS h ON t.HouseId = h.HouseId

JOIN Street AS s ON h.StreetName = s.StreetName

a. columns will be renamed in the result

b. none of the above

c. nothing, it's going to work the same

d. we will get an error

c. nothing, it's going to work the same

500

Reorder the SQL statement that adds a column to the Tenant table in a correct way.

# Block 1 ALTER TABLE Tenant

# Block 2 nvarchar(250)

# Block 3 ADD

# Block 4 InsurancePolicyDetails

# Block 1 ALTER TABLE Tenant

# Block 3 ADD

# Block 4 InsurancePolicyDetails

# Block 2 nvarchar(250)

500

See Modifying Rows 500 image

Tenant, House, Opposite

500

Construct the correct statement that retrieves FirstName and LastName from Person.Person. Add another column into the resultset, where in each row there should be total number of rows from table Person.Person.

SELECT ___,

(SELECT ___ FROM Person.Person) AS TotalPersons

FROM ___ p

A) p.FirstName, p.LastName, Person.Person, COUNT(*)

B) Person.Person, COUNT(*), p.FirstName, p.LastName

C) p.FirstName, p.LastName, COUNT(*), Person.Person