2 Structuered Query Language

If you place multiple actions on a key constraint, do you need to put commas at the end of each actions

... FOREIGN KEY (ManagerID) REFERENCES Employee(ID) ON DELETE SET NULL ON UPDATE CASCADE ); You dont need to place a comma after the SET NULL keyword.

Change the existing ShortDesc column to allow up to 80 characters from the Department table.

ALTER TABLE Department CHANGE ShortDesc ShortDesc VARCHAR(80); even if we arent changing the name we still have to put the name twice. Where the first is current name the 2nd is the new name. the data type follows the new name.

Formats for DATE, TIME, and DATETIME

DATE Format: YYYY-MM-DD. TIME Format: hh:mm:ss DATETIME Format: YYYY-MM-DD hh:mm:ss.

delete the employee table

DROP TABLE Employee;

Constraint exmaples

Primary and foreign keys clauses are considered constraints. PRIMARY KEY(ID, Number)

Long tables may use the LIMIT keyword

Some tables may contain thousands or millions of rows, and selecting all rows can take a long time. MySQL has a LIMIT clause that limits the number of rows returned by a SELECT statement. SELECT * FROM City LIMIT 100;

ALTER TABLE

The ALTER TABLE statement adds, deletes, or modifies columns on an existing table. The ALTER TABLE statement specifies the table name followed by a clause that indicates what should be altered.

USE

The USE statement selects a database and is required to show information about tables within a specific database.

BETWEEN operator

determins if a value is between two values, including the values. HireDate DATE CHECK (HireDate BETWEEN '2000-01-01' AND '2020-01-01'), -- Same as: HireDate >= '2000-01-01' AND HireDate <= '2020-01-01'

... BirthDate DATE DEFAULT '2000-01-01', ...

in this create table statement, If a column is inserted without a BirthDate value it will automatically be set to '2000-01-01'

SHOW CREATE TABLE

shows the CREATE TABLE statement for a given table.

AUTO_INCREMENT

Sets a column to automatically increase in value usually done on a primary key. ID SMALLINT UNSIGNED AUTO_INCREMENT,

CHECK constraint syntax

The first check is a column level constraint. the second is a table level constraint. "If the CHECK expression does not evaluate to TRUE or UNKNOWN (for NULL values), the constraint is violated."

... Username VARCHAR(50) UNIQUE, UNIQUE (Name, Extension), ...

Each username must be unique and that is a column level constraint. Each combination of Name and Extnsion must be unique. Since this is applied to multiple columns is is a table level constraint.

droping constraints is different in MySQL than other databases.

-Most databases support dropping constraints using an ALTER TABLE statement with DROP CONSTRAINT ConstraintName. -In MySQL unique contraints are automatically indexed. They must be removed by: DROP INDEX ConstraintName for a check constraint you must type: DROP CHECK ConstraintName

CONSTRAINT keyword

-if a table is already made, constraints can be added with the ALTER keyword. -If the table has data in it that would violate your new rule, the constraint is rejected.

given this column from a create table statement. add a check constraint to make sure only small, medium, and size can be inserted as values. ... Size VARCHAR(6) ...

... Size VARCHAR(6) CHECK (Size IN ('small', 'medium', 'large')), ... if a row is inserted and value is 'tiny; it is rejected. However, if the size is left blank it is allowed as it evaults to NULL or unknown. this is allowed assuming theres not a business rule against it. such as SIZE VARCHAR(6) NOT NULL

condition

A condition is an expression that evaluates to TRUE, FALSE, or NULL. Only a condition that is TRUE selects the row.

Add a constraint called MgrIdCheck to the existing Department table to ensure ManagerID is 2000 or above.

ALTER TABLE Department ADD CONSTRAINT MgrIDCheck CHECK (ManagerID >= 2000);

Add a column called Salary with datatype decimal with 7 significant digits and two digits after the decimal point to the Employeee table

ALTER TABLE Employee ADD Salary DECIMAL(7,2);

Change the name of the column Salary to AnnualSalary in the Employee table. Make the new data type an integer.

ALTER TABLE Employee CHANGE Salary AnnualSalary INT;

delete the AnnualSalary column from the Employee table

ALTER TABLE Employee DROP AnnualSalary;

it seems a check constraint might be different either in MySQL or when its given a name.

Add a CHECK constraint called OrgCheck that limits OrgCode to 'ACH', 'PLT', or 'WAN' CONSTRAINT OrgCheck CHECK! the CHECK is cut off in the screen shot

Terminology

Clause- a Keyword like INSERT followed by somthing like a table or a column. Keywords are not case sensitive Statement- a Group of clauses. Statements end with a semicolon ;

what selects columns and what selects rows? If there is no where clause, all of rows in the selected column are shown. With a WHERE clause, it will only select the rows under that column that meet a certain condition

SELECT Column1, FROM TableName; this means it will select all the rows in column1 by default. in other words all of the cells directly under column one. will be selected. but if we did a condition WHERE Column1 > 5, It will only select rows under column1 that meet a certain condition.

Which statement shows all the columns in the Country table?

SHOW COLUMNS FROM Country;

what do you use to modify existing Rows? (different from existing columns)

The UPDATE statement modifies existing rows in a table. The UPDATE statement uses the SET clause to specify the new column values. the WHERE clause is used select rows that makes the condition true. If you dont use a WHERE clause it changes all rows.

A constraint is a rule that applies to table data. How would you add a constraint to an exisitng table?

The constraint can be written in teh create table statement. In this case the table already exists so we have to use and ALTER TABLE statement.

... Name VARCHAR(60) NOT NULL, ...

This is a column level constraint that prevents Name from having a NULL value. So when you insert a row you can not leave the Name portion blank.' -NOT NULL must be listed after the data type

FOREIGN KEY constraint

When a foreign key constraint is specified, the database will not allow an insert or update that violates referential integrity. this is due to the default RESTRICT "actions." Zybooks calls this additional 'rule' for foreign and primary keys "actions."