week 4

Question 1

How is date formated in SQL

Answer 1

yyyy-mm-dd

Oct 26 2004 ——> 2004-10-26

Question 2

How is time formated

Answer 2

hh-mm-ss

15:43 ———-> 15-43-21 (15:43:21)

Question 3

what is the format of Create table

Answer 3

Create Table (department name)(
specify attributes
.
.
.
Specify what the primary key is , unique keys, foreign keys etc

Question 4

base tables (tables create through a CREATE TABLE statement ) stored on a file by dbms

Virtual tables( created by a CREATE VIEW statement) not stored on a file

Question 5

How does select work

Answer 5

select [Attribute name}
From [ The table]
where [ Condition]

Question 6

How does Drop table [table name] RESTRICT work

Answer 6

Table is only dropped if not referenced in constraints (eg dependencies between relations through foreign keys …)

Question 7

Drop table cascade

Answer 7

ANy dependency is removed along with the table eg( foreign keys)

Question 8

Drop

Question 9

ORDER BY

Answer 9

DOESNT CHANGER ORDER OF COLUMNS
eg ORDER BY name ( if nothing here default is ascending)
will order all the instances of name in ascending order

IF there is ORDER BY name asc, studentID desc (asc = ascending , desc = descending)

it will order them by name first and if any tuples have the same name, it will order those that have same name by student id ( in descending order)

Question 10

<>

Answer 10

comparison operator that means NOT EQUAL TO
Usually seen in where statements in sql

Question 11

In

Answer 11

eg
select * (all information , all attributes)
From student
where name IN(‘Kate’ , ‘Claire’ , Mike);

selects rows from student where students names are either kate claire or mike

Question 12

How does alter work when adding something
Try add a constraint

Answer 12

ALTER TABLE EMPLOYEE
ADD FOREIGN KEY (DNO) REFERENCES DEPARTMENT (Dnumber);

Question 13

How does alter work when dropping something
Try dropping a constraint

Answer 13

ALTER TABLE DEPARTMENT
DROP FOREIGN KEY (MGRSSN);

Question 14

How does Delete work

Answer 14

DELETE FROM <table name>
WHERE <condition></condition>

DELETE FROM EMPLOYEE
WHERE LNAME=’Brown’

Question 15

How does update work

Answer 15

UPDATE PROJECT
SET PLOCATION = ‘Bellaire’,
DNUM = 5
WHERE PNUMBER=10;