SQL Lite Exercise

The website

SQL Runoob Tutorial
https://www.runoob.com/sqlite/sqlite-tutorial.html

SQL Exercise with Database - the university database schema
https://www.db-book.com/db7/university-lab-dir/sqljs.html

Online SQL Interpreter without demo DB
https://kripken.github.io/sql.js/examples/GUI/index.html

Example

Show the table of SQL Database

select `name`, `sql`
from `sqlite_master`
where type = 'table'

Choose the instructor which the salary is over 10000

select name, salary
from instructor -- From means choose the form
where salary > 10000 -- where means that the conditional statement

The instruction

University DB Data Table

name    sql
classroom    
CREATE TABLE classroom
    (
    building        varchar(15),
         room_number        varchar(7),
         capacity        numeric(4,0),
         primary key (building, room_number)
    )
department    
CREATE TABLE department
    (dept_name        varchar(20), 
     building        varchar(15), 
     budget                numeric(12,2) check (budget > 0),
     primary key (dept_name)
    )
course    
CREATE TABLE course
    (course_id        varchar(8), 
     title            varchar(50), 
     dept_name        varchar(20),
     credits        numeric(2,0) check (credits > 0),
     primary key (course_id),
     foreign key (dept_name) references department
        on delete set null
    )
instructor    
CREATE TABLE instructor
    (ID            varchar(5), 
     name            varchar(20) not null, 
     dept_name        varchar(20), 
     salary            numeric(8,2) check (salary > 29000),
     primary key (ID),
     foreign key (dept_name) references department
        on delete set null
    )
section    
CREATE TABLE section
    (course_id        varchar(8), 
         sec_id            varchar(8),
     semester        varchar(6)
        check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
     year            numeric(4,0) check (year > 1701 and year < 2100), 
     building        varchar(15),
     room_number        varchar(7),
     time_slot_id        varchar(4),
     primary key (course_id, sec_id, semester, year),
     foreign key (course_id) references course
        on delete cascade,
     foreign key (building, room_number) references classroom
        on delete set null
    )
teaches    
CREATE TABLE teaches
    (ID            varchar(5), 
     course_id        varchar(8),
     sec_id            varchar(8), 
     semester        varchar(6),
     year            numeric(4,0),
     primary key (ID, course_id, sec_id, semester, year),
     foreign key (course_id,sec_id, semester, year) references section
        on delete cascade,
     foreign key (ID) references instructor
        on delete cascade
    )
student    
CREATE TABLE student
    (ID            varchar(5), 
     name            varchar(20) not null, 
     dept_name        varchar(20), 
     tot_cred        numeric(3,0) check (tot_cred >= 0),
     primary key (ID),
     foreign key (dept_name) references department
        on delete set null
    )
takes    
CREATE TABLE takes
    (ID            varchar(5), 
     course_id        varchar(8),
     sec_id            varchar(8), 
     semester        varchar(6),
     year            numeric(4,0),
     grade                varchar(2),
     primary key (ID, course_id, sec_id, semester, year),
     foreign key (course_id,sec_id, semester, year) references section
        on delete cascade,
     foreign key (ID) references student
        on delete cascade
    )
advisor    
CREATE TABLE advisor
    (s_ID            varchar(5),
     i_ID            varchar(5),
     primary key (s_ID),
     foreign key (i_ID) references instructor (ID)
        on delete set null,
     foreign key (s_ID) references student (ID)
        on delete cascade
    )
time_slot    
CREATE TABLE time_slot
    (time_slot_id        varchar(4),
     day            varchar(1),
     start_hr        numeric(2) check (start_hr >= 0 and start_hr < 24),
     start_min        numeric(2) check (start_min >= 0 and start_min < 60),
     end_hr            numeric(2) check (end_hr >= 0 and end_hr < 24),
     end_min        numeric(2) check (end_min >= 0 and end_min < 60),
     primary key (time_slot_id, day, start_hr, start_min)
    )
prereq    
CREATE TABLE prereq
    (course_id        varchar(8), 
     prereq_id        varchar(8),
     primary key (course_id, prereq_id),
     foreign key (course_id) references course
        on delete cascade,
     foreign key (prereq_id) references course
    )

HR Data Table

name    sql
countries    
CREATE TABLE countries(
country_id varchar(2) primary key  not null,
country_name varchar(40) not null,
region_id integer(2) not null)
regions    
CREATE TABLE regions(
region_id integer(2) primary key not null,
region_name varchar(25) not null)
locations    
CREATE TABLE locations(
location_id integer(4) primary key not null,
street_address varchar(25) not null,
postal_code varchar(12),
city varchar(30) not null,
state_province varchar(12),
country_id varchar(2))
departments    
CREATE TABLE departments(
department_id integer(3) primary key not null,
depart_name varchar(20) not null,
manager_id integer(3) not null,
location_id integer(4))
jobs    
CREATE TABLE jobs(
job_id varchar(10) primary key not null,
job_title varchar(25) not null,
min_salary decimal,
max_salary decimal)
employees    
CREATE TABLE employees(
employee_id integer(3) primary key not null,
first_name varchar(20),
last_name varchar(25),
email varchar(25),
phone_number varchar(20),
hire_date date,
job_id varchar(10) not null,
salary decimal,
commission_pct number,
manager_id integer(3),
department_id integer(3), Avg_Salary NUMERIC)
department    
CREATE TABLE department(
  "department_id" TEXT,
  "department_name" TEXT,
  "manager_id" TEXT,
  "location_id" TEXT
)
job_history    
CREATE TABLE job_history(
employee_id integer(3) not null,
start_date date not null,
end_date date not null,
job_id varchar(10) not null,
department_id integer(3) not null)
prod_mast    
CREATE TABLE prod_mast(
prod_id integer PRIMARY KEY,
prod_name text(20),
prod_rate integer,
prod_qc text(20) DEFAULT 'OK')
prod_backup    
CREATE TABLE prod_backup(
prod_id integer PRIMARY KEY,
prod_name text(20),
prod_rate integer,
prod_qc text(10) DEFAULT 'OK')
orders    
CREATE TABLE orders(
ord_no integer PRIMARY KEY,
item_id integer ,
item_name text(20),
ord_qty integer,
cost integer)
tb1    
CREATE TABLE tb1 (c1 INT, c2 CHAR(5), c3 FLOAT)
ESERCICIO1    
CREATE TABLE ESERCICIO1(C TEXT, D TEXT)
users    
CREATE TABLE users(name varchar(128), email varchar(128))
tags    
CREATE TABLE tags (
    title TEXT,
    description TEXT,
    created TEXT
)
s    
CREATE TABLE s( A int, D int, E int)
r    
CREATE TABLE r (A int, B int)
Emor    
CREATE TABLE Emor(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT)
MIN_SALARY    
CREATE TABLE MIN_SALARY(job_id TEXT,MIN_SALARY)
employee_data    
CREATE TABLE employee_data (employee_name TEXT, item TEXT, rate REAL, quantity INTEGER, date TEXT, id INTEGER PRIMARY KEY)
STUDENT    
CREATE TABLE STUDENT(  
   ID INT PRIMARY KEY     NOT NULL,  
   NAME           TEXT    NOT NULL,  
   AGE            INT     NOT NULL,  
   ADDRESS        CHAR(50),  
   FEES         REAL  
)
EMPLOYEE_INCOME    
CREATE TABLE EMPLOYEE_INCOME 
(EMPID NUMBER(10), 
NAME VARCHAR2(20), 
SALARY NUMBER(10))
details    CREATE TABLE details
(id      number(8,0),
name     varchar2(50),
weight   number(8,0),
turn     number(8,0)
)