T03 SQL DDL PDF

INSTITUTO SUPERIOR DE ENGENHARIA DO PORTO DATABASES T03 – SQL DATA DEFINITION LANGUAGE SUBSET (DDL) INSTITUTO SUPERIOR DE ENGENHARIA DO PORTO SQL- STRUTURED QUERY LANGUAGE SQL is more than a query language. Includes features for defining the data structure, for changing data in a database, an...

INSTITUTO SUPERIOR DE ENGENHARIA DO PORTO DATABASES T03 – SQL DATA DEFINITION LANGUAGE SUBSET (DDL) INSTITUTO SUPERIOR DE ENGENHARIA DO PORTO SQL- STRUTURED QUERY LANGUAGE SQL is more than a query language. Includes features for defining the data structure, for changing data in a database, and for specifying security schemes. 2 INSTITUTO SUPERIOR DE ENGENHARIA DO PORTO CREATE TABLE The CREATE TABLE statement is used to create a new table in a database. – The column parameters specify the names of the columns of the table. – The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.). – https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLE.html CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype,.... ); The DROP TABLE statement is used to delete a table. 3 INSTITUTO SUPERIOR DE ENGENHARIA DO PORTO SOME ORACLE DATA TYPES Data Type Description VARCHAR2(size) Variable-length character string having maximum length size bytes or characters. NVARCHAR2(size) Variable-length Unicode character string having maximum length size characters. NUMBER [ (p [, s]) ] Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. Both precision and scale are in decimal digits. A NUMBER value requires from 1 to 22 bytes. FLOAT [(p)] A subtype of the NUMBER data type having precision p. A FLOAT value is represented internally as NUMBER. The precision p can range from 1 to 126 binary digits. A FLOAT value requires from 1 to 22 bytes. DATE Valid date range from January 1, 4712 BC, to December 31, 9999 AD. TIMESTAMP [(fractional_seconds_precision)] Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. 4 INSTITUTO SUPERIOR DE ENGENHARIA DO PORTO CREATE TABLE CONSTRAINTS In SQL, constraints of various types can be defined. – Integrity constraints: define the primary key and foreign key with the referenced table and primary key. – Value constraints: define whether null values are not allowed, whether unique values are required, and whether only a certain set of values are allowed in a column A constraint can be created at the same time the table is created, or it can be added to the table later. There are two levels at which a constraint is defined: – Column level - refers to only one column and is described in front of the column in question); – Table level - refers to more than one column and is separate from the definition of the columns). 5 INSTITUTO SUPERIOR DE ENGENHARIA DO PORTO DDL EXAMPLE A possible solution for the GoldFish problem 6 INSTITUTO SUPERIOR DE ENGENHARIA DO PORTO CREATE TABLE EXAMPLE RaceType and Style tables value constraints CREATE TABLE RaceType ( StyleID number(4) NOT NULL, Distance number(4) NOT NULL CHECK(Distance BETWEEN 50 and 1500), PRIMARY KEY (StyleID, Distance)); CREATE TABLE Style ( StyleID number(4) GENERATED AS IDENTITY, Name varchar2(50), PRIMARY KEY (StyleID)); ALTER TABLE RaceType ADD CONSTRAINT FKRaceType641056 FOREIGN KEY (StyleID) REFERENCES Style (StyleID); 7 INSTITUTO SUPERIOR DE ENGENHARIA DO PORTO CREATE TABLE EXAMPLE Coach and Coaching tables value constraints CREATE TABLE Coach ( Code number(10) GENERATED AS IDENTITY, Name varchar2(255) NOT NULL, Gender char(1) NOT NULL CHECK (Gender IN (‘M’, ‘F’)) , Phone number(13) NOT NULL, PRIMARY KEY (Code)); CREATE TABLE Coaching ( SwimmerCode number(10) NOT NULL, CoachCode number(10) NOT NULL, StartDate date NOT NULL, EndDate date, PRIMARY KEY (SwimmerCode, CoachCode)); ALTER TABLE Coaching ADD CONSTRAINT FKCoaching964042 FOREIGN KEY (SwimmerCode) REFERENCES Swimmer (Code); ALTER TABLE Coaching ADD CONSTRAINT FKCoaching917721 FOREIGN KEY (CoachCode) REFERENCES Coach (Code); 8 INSTITUTO SUPERIOR DE ENGENHARIA DO PORTO CREATE TABLE EXAMPLE RaceType and Style table integrity constraints CREATE TABLE RaceType ( StyleID number(4) NOT NULL, Distance number(4) NOT NULL CHECK(Distance >=50 and Distance0), PRIMARY KEY (StyleID, Distance)); CREATE TABLE Style ( StyleID number(4) GENERATED AS IDENTITY, Name varchar2(50), PRIMARY KEY (StyleID)); ALTER TABLE RaceType ADD CONSTRAINT FKRaceType641056 FOREIGN KEY (StyleID) REFERENCES Style (StyleID); 9 INSTITUTO SUPERIOR DE ENGENHARIA DO PORTO CREATE TABLE EXAMPLE Swimer and RaceResult table integrity constraints CREATE TABLE Swimmer ( Code number(10) GENERATED AS IDENTITY, Name varchar2(255) NOT NULL, Address varchar2(255) NOT NULL, BirthDate date NOT NULL, IDCategory number(5) NOT NULL, PRIMARY KEY (Code)); CREATE TABLE RaceResult ( SwimmerCode number(10) NOT NULL, RaceCode number(10) NOT NULL, Cassification char(2) NOT NULL, Time timestamp(7) NOT NULL, PRIMARY KEY (SwimmerCode, RaceCode)); ALTER TABLE Swimmer ADD CONSTRAINT FKSwimmer182597 FOREIGN KEY (IDCategory) REFERENCES Category (IDCategory); ALTER TABLE RaceResult ADD CONSTRAINT FKRaceResult377630 FOREIGN KEY (SwimmerCode) REFERENCES Swimmer (Code); ALTER TABLE RaceResult ADD CONSTRAINT FKRaceResult251735 FOREIGN KEY (RaceCode) REFERENCES Race (Code); 10 INSTITUTO SUPERIOR DE ENGENHARIA DO PORTO CREATE TABLE EXAMPLE Race table integrity constraints CREATE TABLE Race ( Code number(10) GENERATED AS IDENTITY, ChampionshipEditionCode number(10) NOT NULL, StyleID number(4) NOT NULL, Distance number(4) NOT NULL, PRIMARY KEY (Code)); ALTER TABLE Race ADD CONSTRAINT FKRace456686 FOREIGN KEY (ChampionshipEditionCode) REFERENCES ChampionshipEdition (Code); ALTER TABLE Race ADD CONSTRAINT FKRace878877 FOREIGN KEY (StyleID, Distance) REFERENCES RaceType (StyleID, Distance); 11 INSTITUTO SUPERIOR DE ENGENHARIA DO PORTO FURTHER READING Begg C., Connolly T., Database Systems: A Practical Approach to Design, Implementation, and Management, Pearson, 6th ed. 2015 – Chapter 7 – SQL: Data Definition 12