SQLQueryBasico.sql

/*
# 1\. Create a new user and login (optional)
*/

/*
# 2\. Create a new database and Schema
*/
 /* Nombres de database en este formato */
CREATE DATABASE DB_JP_SQL_BASICO
/* Ejecuta un batch de operaciones */
GO

/* USE me permite empezar a usar la base de datos, como seleccionarla */
USE DB_JP_SQL_BASICO
GO
CREATE SCHEMA UNIVERSITY
GO

/*
# 3\. Create objects / tables
*/

USE DB_JP_SQL_BASICO
GO

/*STUDENT*/

CREATE TABLE STUDENT (
    EMAIL VARCHAR(255) PRIMARY KEY,
    FIRST_NAME VARCHAR(255),
    LAST_NAME VARCHAR(255),
    CARNET CHAR(6),
    UNIQUE(CARNET),
    IMAGE VARBINARY -- This is an IMAGE attribute of STUDENT relation, a set of bits.
)

/*INSTRUCTOR*/

CREATE TABLE INSTRUCTOR (
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   -- IDENTITY es �nico de SQL Server Microsoft. auto incrementales aumenta en 1 a 1 el ID.
   -- Asigna a cada Instructor un ID desde 1 hasta N, pero si se elimina un instructor no se reasigna el numero.
)

/*COURSE*/

CREATE TABLE COURSE (
    ACRONYM CHAR(6) NOT NULL,
    /* ACRONYM CHAR(6) PRIMARY KEY*/
    NAME VARCHAR(50) NOT NULL,
    CREDITS INT NOT NULL,
    CHECK (CREDITS > 0 AND CREDITS < 13), -- Simple Validation only for SQL Server.
    UNIQUE (NAME),
    PRIMARY KEY (ACRONYM)
)

/*GROUP*/

CREATE TABLE [GROUP] ( -- It has "[]" 'cause is a reserved keywords of SQL
    NUMBER SMALLINT,
    SEMESTER SMALLINT,
    YEAR SMALLINT,
    ACRONYM CHAR (6) NOT NULL,
    PRIMARY KEY (NUMBER,SEMESTER,YEAR,ACRONYM),
    FOREIGN KEY (ACRONYM) REFERENCES COURSE(ACRONYM)
)

/*IMPARTS*/

CREATE TABLE IMPARTS (
    ID INT,
    NUMBER SMALLINT,
    SEMESTER SMALLINT,
    YEAR SMALLINT,
    ACRONYM CHAR (6) NOT NULL
    PRIMARY KEY (ID,NUMBER,SEMESTER,YEAR,ACRONYM),
    FOREIGN KEY (ID) REFERENCES INSTRUCTOR(ID),
	FOREIGN KEY (NUMBER,SEMESTER,YEAR,ACRONYM) REFERENCES [GROUP](NUMBER,SEMESTER,YEAR,ACRONYM)
)

/*TAKES*/

CREATE TABLE TAKES (
    EMAIL VARCHAR(255),
    NUMBER SMALLINT,
    SEMESTER SMALLINT,
    YEAR SMALLINT,
    ACRONYM CHAR (6),
	-- Set an alias to PRIMARY KEY
    CONSTRAINT PK_TAKES PRIMARY KEY (EMAIL,NUMBER,SEMESTER,YEAR,ACRONYM),
	FOREIGN KEY (NUMBER,SEMESTER,YEAR,ACRONYM) REFERENCES [GROUP](NUMBER,SEMESTER,YEAR,ACRONYM),
	-- Which are the delete behavior for the FOREIGN KEY
    FOREIGN KEY (EMAIL) REFERENCES STUDENT(EMAIL)
        ON DELETE NO ACTION -- Default, Nothing happens.
        ON UPDATE NO ACTION
        /*
            ON DELETE NO ACTION /*default behavior*/
            ON DELETE SET NULL
            ON DELETE SET DEFAULT
            ON DELETE CASCADE  Recursive Delete
            ON UPDATE NO ACTION
            ON UPDATE SET NULL
            ON UPDATE SET DEFAULT
            ON UPDATE CASCADE
        */
)

/*INSTRUCTOR_UNIVERSITY_DEGREES*/

CREATE TABLE INSTRUCTOR_UNIVERSITY_DEGREES (
    ID INT,
    DEGREE VARCHAR,
    CHECK (DEGREE IN ('MASTER','PHD','LIC')), -- IN: Only register the values in a list.
    PRIMARY KEY (ID,DEGREE),
    FOREIGN KEY (ID) REFERENCES INSTRUCTOR (ID)
)

/*
# 4\. Data manipulation language
*/

/*
## 4.1 INSERT
*/

USE DB_JP_SQL_BASICO
GO

CREATE TABLE SCHOOL (
    NAME VARCHAR(255),
    ACRONYM CHAR(6),
    UNIQUE (ACRONYM),
    PHONE_NUMBER VARCHAR(8),
    NUM_OF_STUDENTS INT DEFAULT 0
    PRIMARY KEY (NAME)
)

USE DB_JP_SQL_BASICO
GO

INSERT INTO
    SCHOOL (NAME,ACRONYM,PHONE_NUMBER,NUM_OF_STUDENTS)
VALUES
    ('Ciencias de la Computacion', 'ECCI', '22118000', 888),
    ('Ciencias de la Comunicacion Colectiva', 'ECCC', '22113600', 999),
    ('Lenguas Modernas', 'ELM', '22118391', NULL),
    ('Administracion de Negocios', 'EAN', '22119180', 3000),
    ('Antropologia', 'EAT', '22116458', 500),
    ('Matematica', 'EMat', '22116551', 1500)

/*
## 4.2 SELECT
*/

SELECT NAME,ACRONYM,PHONE_NUMBER,NUM_OF_STUDENTS
FROM SCHOOL

-- Refined Scope
SELECT NAME,ACRONYM,PHONE_NUMBER,NUM_OF_STUDENTS
FROM SCHOOL
WHERE NUM_OF_STUDENTS IS NULL

SELECT *
FROM SCHOOL
WHERE NUM_OF_STUDENTS IS NOT NULL
ORDER BY ACRONYM ASC /*DESC*/


SELECT TOP (2) *
FROM SCHOOL
WHERE NUM_OF_STUDENTS IS NOT NULL
ORDER BY ACRONYM ASC /*DESC*/

/*
## 4.3 UPDATE
*/

UPDATE SCHOOL
SET NUM_OF_STUDENTS = NUM_OF_STUDENTS + 1


USE DB_JP_SQL_BASICO
GO

UPDATE SCHOOL
SET NAME = 'Escuela de Ciencias de la Computacion e Informatica'
WHERE ACRONYM = 'ECCI'

SELECT NAME,ACRONYM,PHONE_NUMBER,NUM_OF_STUDENTS
FROM SCHOOL

/*
## 4.4 DELETE
*/

DELETE FROM SCHOOL

DELETE FROM SCHOOL
WHERE NUM_OF_STUDENTS = 1

/*
## 4.5 Aggregation functions
*/

SELECT AVG(NUM_OF_STUDENTS) FROM SCHOOL;

SELECT COUNT(*) FROM SCHOOL;

SELECT MIN(NUM_OF_STUDENTS) FROM SCHOOL;

SELECT MIN(NUM_OF_STUDENTS), MAX(NUM_OF_STUDENTS) FROM SCHOOL;