Permalink
Cannot retrieve contributors at this time
Name already in use
A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?
Scripts-SQL-teoria/tables.sql
Go to fileThis commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.
38 lines (35 sloc)
1.23 KB
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* Scripts de apoyo al tema de introducción al SQL. | |
Autores: Inma Hernández y David Ruiz | |
Fecha creación: Octubre de 2019 | |
Descripción: material de apoyo para las clases de teoría de SQL. */ | |
/* Eliminación de tablas */ | |
DROP TABLE IF EXISTS Employees; | |
DROP TABLE IF EXISTS Departments; | |
/* Es importante el orden en el que se borran las tablas. Pruebe a cambiar el orden y observe el | |
resultado */ | |
/* Relación: Departments(!departmentId, #(nameDep, city)) */ | |
CREATE TABLE Departments( | |
departmentId INT NOT NULL AUTO_INCREMENT, | |
nameDep VARCHAR(32), | |
city VARCHAR(64), | |
PRIMARY KEY(departmentId), | |
UNIQUE(nameDep, city) | |
); | |
/* Relación: Employees(!employeeId, @departmentId, @bossId, #nameEmp, salary, startDate, | |
endDate, fee) */ | |
CREATE TABLE Employees( | |
employeeId INT NOT NULL AUTO_INCREMENT, | |
departmentId INT, | |
bossId INT, | |
nameEmp VARCHAR(64) NOT NULL, | |
salary DECIMAL(6,2) DEFAULT 2000.00, | |
startDate DATE, | |
endDate DATE, | |
fee DOUBLE, | |
PRIMARY KEY(employeeId), | |
FOREIGN KEY(departmentId) REFERENCES Departments(departmentId) ON DELETE SET NULL, | |
FOREIGN KEY(bossId) REFERENCES Employees(employeeId), | |
UNIQUE(nameEmp), | |
CHECK (fee >=0 AND fee <=1), | |
CONSTRAINT contractDate CHECK (startDate < endDate) | |
); |