database quiz

foreign key constraint violation

1. Insert or update a Employee tuple that refers to a nonexistent Dept in the Department table. Always rejected 2. Delete or update Dept in the Department table that some Employee tuples refer to. Reject by default CASCADE SET NULL or SET DEFAULT Ex: FOREIGN KEY (DeptNumber) REFERENCES Department ON DELETE CASCADE ON UPDATE SET DEFAULT(000) Case 1: If Dept 123 is deleted from Department table, all tuples in Employee table with DeptNumber 123 will be automatically deleted. Case 2: If Dept 123 is updated to 100 in the Department table, the value of Dept in Employee tuples which is 123 will be automatically set to 000.

Execute a SQL file

@filename.sql

check conditions

A check condition is a Boolean expression: E.g.: x>5 and x<10 Attribute-Based Check Tuple-Based Check

Adding a column

ALTER TABLE Employee ADD COLUMN mname VARCHAR(20);

adding constraints

ALTER TABLE Employee ADD check(age>0);

Dropping columns:

ALTER TABLE Employee DROP COLUMN Mname CASCADE;

Changing columns' definition:

ALTER TABLE Employee Modify mname varchar(10);

constraints in create table

Adding constraints to a table enables the database system to enforce data integrity. However, adding constraints also makes inserting data slower.

delete a table

Basic Format: DROP TABLE Table_name RESTRICT; DROP TABLE Table_name CASCADE; "RESTRICT": drop the table only when it is not referenced. "CASCADE": will drop the constraints and views that reference the table DROP TABLE command remove all records in the table and the table definition.

foreign constraint

CREATE TABLE Employee ( ID INTEGER primary key, Fname VARCHAR(20), Lname VARCHAR(20), Gender CHAR(1), Salary INTEGER NOT NULL, Dept INTEGER REFERENCES Department(DeptNumber) );

giving constraint name

CREATE TABLE Employee ( ID INTEGER, Fname VARCHAR(20), Lname VARCHAR(20), Gender CHAR(1), Salary INTEGER NOT NULL, CONSTRAINT Dept_Primarykey Primary key (ID) ); Note 1: names of the constraints in the same relation must be unique Note 2: it is useful when the constraint needs to be dropped later.

basic format of the CREATE TABLE

CREATE TABLE TableName( Column1 DataType1 UNIQUE ColConstraint, ... ColumnN DataTypeN ColConstraint default(...), PRIMARY KEY(col1, coln) TableConstraint1, ... TableConstraintM );

unique values

Each table can have only one Primary key, but many UNIQUE's. SQL does not allow NULL in primary key, but allows them in "unique" columns (which may have two or more nulls, but not repeated non-null values).

attribute-based check

Follow an attribute by a condition that must hold for that attribute in each tuple of its relation. Condition is checked only when the associated attribute changes (i.e., an insert or update occurs). ex: CREATE TABLE Employee( ID INTEGER primary key, Fname VARCHAR(20), Lname VARCHAR(20), Gender CHAR(1) check(gender='F' or gender='M'), Salary INTEGER check(Salary>=0), DeptNumber INTEGER );

deleting rows

General format: DELETE FROM Table  WHERE Condition; Deletes all rows satisfying Condition from Table The Condition can also involve SQL queries. Example: DELETE FROM Employee WHERE Deptnumber IN (Select Dnumber From Department Where Dname = `Research');

update rows

General format: UPDATE Table SET Column1=value1,...,ColumnN=valueN WHERE Condition Ex: UPDATE Employee  SET Salary = 100000 WHERE Salary > 100000; UPDATE Employee SET Salary = Salary*1.1 WHERE Deptnumber IN (Select Dnumber From Department Where Dname = `Research');

data types

INT or INTEGER. REAL or FLOAT. CHAR(n) = fixed length character string, padded with "pad characters." VARCHAR (n) = variable-length strings up to n characters. NUMERIC(precision, decimal) is a number with precision digits with the decimal point decimal digits from the right. NUMERIC(10,2) can store ±99,999,999.99 DATE and TIME Others Boolean Interval ......

types of constraints

Not Null Default Values Unique Primary Key Foreign Key Check Condition

tuple-based check

Separate element of table declaration. Checked whenever a tuple is inserted or updated. ex: CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price REAL, PRIMARY KEY (bar,beer), CHECK (bar = 'Joe''s Bar' OR price <= 5.00) );

altering tables

Table definitions can be altered after their creation Use the reserved words ALTER TABLE Options: Adding columns: ADD COLUMN Changing columns' definition: ALTER COLUMN Dropping columns: DROP COLUMN Adding or dropping constraints: ADD, DROP CONSTRAINT And more...

insert a row

To insert a row into the Employee table Employee(id:integer,fname:varchar(20),gender:varchar(1), deptnumber:integer, salary:integer) will be rejected if it violates the constraints specified in the table definition. Approach 1: The attributes don't have to be specified if values are specified for all columns and in the order defined by the table Insert Into Employee Values (122,'Bob','M',12,110000); Approach 2: Only few of attributes are assigned values for the new tuple. SQL> Insert Into Employee(id,deptnumber) Values(123,13); Approach 3: use the SQL query to retrieve data from other tables and then insert them into the current table SQL> Insert Into Employee(id,fname) Select [attr1], [attr2] From [table_name] Where [condition];

show the structure of a table

desc table_name

Create a .sql file

ed filename.sql