Midterm Practical

Question 1

Create a database

Answer 1

CREATE DATABASE databaseName;

Question 2

Open a database

Answer 2

USE databaseName;

Question 3

Create a table with attributes

Answer 3

CREATE TABLE employee(
employeeID CHAR(11),
ssn CHAR(11),
name VARCHAR(35),
dob DATE,
CONSTRAINT employee_pk PRIMARY KEY(employeeID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Question 4

Remove a table within the current database

Answer 4

DROP tableName;

Question 5

Remove a database

Answer 5

DROP DATABASE databaseName;
DROP DATABASE IF EXISTS test;

Question 6

View metadata commands

Answer 6

SHOW DATABASES; SHOW TABLES; DESCRIBE tableName;

Question 7

Run a script

Answer 7

. filepath.sql

Question 8

INSERT syntax

Answer 8

INSERT INTO tableName
(filedlist) VALUES (valuelist);

Question 9

SELECT all values

Answer 9

SELECT *
FROM tableName;

Question 10

Select some values

Answer 10

SELECT attributes_to_include
FROM tableName;

Question 11

Select with a column alias

Answer 11

SELECT attribute as “longer attribute”
FROM tableName;

Question 12

WHERE clause syntax

Answer 12

SELECT * | attributes_to_include
FROM tableName
WHERE condition(s);

Question 13

Logical Operators

Answer 13

NOT, AND, OR

Question 14

How to find NULL values

Answer 14

SELECT employeeID
FROM employee
WHERE name IS NULL;

Question 15

INSERT NULL values

Answer 15

INSER INTO tableNAME (test, test2)
VALUES(‘3333’, NULL);

Question 16

SMALLINT

Answer 16

-32768 to 32768 OR 0 to 65535

Question 17

DECIMAL

Answer 17

DECIMAL(p,s)
§ Example: DECIMAL(5,2) -> ###.##

Question 18

SIGNED VS UNSIGNED

Answer 18

UNSIGNED can store only zero and positive numbers in a column. SIGNED can hold zero, negative and positive

Question 19

How to apply a not null constraint

Answer 19

title VARCHAR(255) NOT NULL,

Question 20

Set default constraint

Answer 20

replacementCost DECIMAL(5,2) NOT NULL DEFAULT 19.99,

Question 21

Relational Operators

Answer 21

> Greater Than
= Greater Than or Equal To
< Less Than
<= Less Than or Equal To
= Equal To
<> Not Equal To
!= Not Equal To
IN (list) Contained in comma-separated list
LIKE string Matches string pattern

Question 22

BETWEEN

Answer 22

will include values that are greater than, or equal to, the minimum
value and less than, or equal to, the maximum value specified

SELECT * | attribute(s)
FROM table
WHERE attribute BETWEEN min_value AND max_value;

Question 23

USING LIKE with WILDCARDS

Answer 23

To specify our pattern we use characters and wildcards
§ Characters must be present
§ Wildcards are placeholders for characters
§ _ means exactly 1 character
§ % means 0 or more characters

Question 24

Using select calculations

Answer 24

SELECT title, length, TRUNCATE(length/60, 0), length MOD 60
FROM film;

SELECT title, length AS “Total Minutes”,
TRUNCATE(length/60,0) AS “Hours”, length MOD 60 AS “Minutes”
FROM film;

Question 25

UPDATE syntax

Answer 25

Modifies existing user data
General Syntax Format:
UPDATE tableName
SET field1 = value1, …, fieldN = valueN
WHERE condition;

Question 26

DELETE syntax

Answer 26

Removes record(s) from a table
General Syntax Format:
DELETE
FROM tableName
WHERE condition(s);

Question 27

ALTER table to add/drop primary key

Answer 27

Let’s get rid of the PRIMAY KEY constraint:
ALTER TABLE film
DROP PRIMARY KEY;

Then add it back:
ALTER TABLE film
ADD PRIMARY KEY film_pk (filmID);

Question 28

add/drop columns

Answer 28

General Syntax Format:
ALTER TABLE tableName
{ADD|DROP} COLUMN columnspec;

Question 29

changing defaults

Answer 29

Defaults are the only “property” that you can alter.
Let’s add a default value of “Not entered.” to the description column in the table:
ALTER TABLE film
ALTER description SET DEFAULT ‘Not entered.’;

And if we change our mind and don’t want it after all:
ALTER TABLE film
ALTER description DROP DEFAULT;

Question 30

Modifying columns

Answer 30

ALTER TABLE tableName
MODIFY COLUMN attribute DATATYPE [NOT NULL] [DEFAULT];

Question 31

1NF to BCNF breakdowns

Answer 31

1NF — no repeating groups, attr names are unique, 1NF - has primary key, no repeating attributes

2NF — partial dependencies (functional dependencies are only part of the primary key)

3NF— transitive dependencies on non-key elements to non-key elements.

BCNF — check that left hand side of functional dependencies are candidate keys.