I want to extend my series of codes reminder tutorials. So I would like to begin a new course module named MySQL.


-- Creating a database
-- To explain easily I take company as the database name.

create database company

-- Delete the database if exists

DROP DATABASE IF EXISTS Company;

-- Create the db if not found any

CREATE DATABASE IF NOT EXISTS Company;


-- Show All databases

show databases;

-- we need to use the database

use Company;

-- Creating a table (name department)

create table department (
dname varchar(10) not null,
dnumber integer not null,
managerid char(9),
managerdate char(9)
);

More data types -> integer,float, decimal( i , j),
char(n), varchar(n),date ,time , timestamp ,
interval (Specifies a relative value -> ex: day/time intervals)



-- Adding Primary Key

create table Ordertbl(
    oid int not null,
    name varchar(10),
    adress varchar(20),
    constraint pk_orderid primary key(oid)   
   
);

-- Adding Foreign Keys
 


create table venue(
Venue_id varchar(20),
name varchar(20),
capacity int,
Adress varchar(50),
constraint venue_pk primary key(Venue_id)  


);


create table Ticket(

Ticket_type varchar(20),
Number_of_tickets int,
Number_of_tickets_sold int,
Venue_id varchar(20),
constraint ticket_pk primary key(Ticket_type),
constraint ticket_fk foreign key(Venue_id)
        references venue(Venue_id) on delete set null

);


-- Multiple primary keys

create table project_hours(
pnumber integer not null,
empno integer not null,
worked_hours integer,
primary key (pnumber, empno)

)

Multiple Foreign Keys

create table Works(
    eid integer,
    did varchar(20),
    pctTime integer,
    primary key (eid,did),
    foreign key (eid) references Emp (eid),
    foreign key (did) references Dept (did)
);


Sometimes We cannot initiate a foreign key when creating the table
because of the reference table we will be creating afterwords.
So we need to alter the table after the tables are created.

-- Altering the table

alter table Person
add constraint fk_person foreign key(oid) references Ordert(oid);

-- Adding new column into employee table

alter table employee add job varchar(12);

(The database users must still enter a value
for the new attribute job for each employee tuple (one row).
This can be done using the UPDATE command.)

-- Dropping a table

drop table department;


-- Inserting Data into the Table

insert into Employee (eid,ename,age,salary) values (1,'Saman',20,20000.00);
   --or
insert into Employee values (1,'Saman',20,20000.00);


-- Updating data

update Employee
set name = "Kamal", age = 5
where eid=10

-- Deleting Data

delete from employee
where ename='brown’