1. Mastering SQL for Beginners:

A Step-by-Step Guide to Learning Structured Query Language

Setting Up Your MySQL Environment

Day:1 – create a database called ‘assignments’

Create the tables and Enter the records

Understanding Database Concepts:

DDL commands by alter and drop :

alter table students add column marks integer;
alter table students drop column age;
alter table students change column sid std_id integer;
alter table students modify column sname varchar(20);
alter table students rename to mystudents; or rename table mystudents to students;
alter table students add column sno integer first;

drop table students;

DML commands:

update students
set sname = 'Thomas'
where std_id =3;

update students set marks = 0 where course is null;

update students 
set course = 'Dance',marks = 55, sname = 'James'
where std_id = 4;

delete from students where course = 'magic';

Truncate = drop+ create

In truncate, all database objects are removed and table is created fresh.

Before truncate use this command to know the cmd used to create table;

show create table students;

Date data type in MYSQL;

DQL commands:

select first_name, last_name, salary, salary*0.2 as bonus from myemp limit 10;

select first_name, last_name, salary, salary*0.2 as bonus, salary+salary*0.2 as bonus as total_salay from myemp limit 10;

Arranging records in descending order:

select * from myemp where salary >= 10000;

select * from myemp where hire_date > '2000-12-31';

Executing SQL Statements:

Step:1

Create a table called countries with the following columns name, population, capital   by choosing choose appropriate datatypes for the columns

Step:2

Insert the data into the table

Step:3:

Add a couple of countries of your choice

Step:4

Change ‘Delhi’ to ‘New Delhi’

Rename the table countries to big_countries .

Creating table with Fields and Constraints:

create table journey(
bus_id integer not null,
bus_name varchar (255) not null,
source_station varchar(255) not null,
destination varchar(255) not null,
email varchar(255) not null,
constraint journey_pk primary key (bus_ID),
constraint email_unique unique (email)
);
create table vendor(
vendor_Id int primary key,
name varchar (50) not null,
email varchar(50) unique,
country varchar(50) default "N/A"
);
CREATE TABLE movies (
    Movie_ID INT NOT NULL,
    Name VARCHAR(50) NOT NULL,
    Release_Year VARCHAR(4) DEFAULT '-',
    Cast VARCHAR(255) NOT NULL,
    Gender ENUM('Male', 'Female'),
    No_of_shows INT CHECK (No_of_shows > 0),
    CONSTRAINT movie_id_unique UNIQUE (Movie_ID),
    PRIMARY KEY (Movie_ID)
);
create table product_stock(
 product_id Int auto_increment primary key,
 product_name varchar(255) not null unique,
 description text
 );
INSERT INTO product_stock(product_id, product_name, description)
 VALUES
 (1001,'samsung', 'Spigen Samsung Galaxy S5 Case Wallet'),
 (1002,'apple', 'Apple iPhone 5C'),
 (1003,'logitech', 'Wireless Gaming Headset G930');
 CREATE TABLE Suppliers_stock(
    supplier_id INT AUTO_INCREMENT PRIMARY KEY,
    supplier_name VARCHAR(255),
    location VARCHAR(255),
    parent_supplier_id INT,
    FOREIGN KEY (parent_supplier_id) REFERENCES Suppliers_stock(supplier_id)
    on delete cascade
    on update cascade
    );
INSERT INTO Suppliers_stock (supplier_name, location, parent_supplier_id)
    VALUES 
    ('Supplier A', 'United states', NULL),
    ('Supplier B', 'Location B', 1),
    ('Supplier C', 'Location C', 2);
    
ALTER TABLE Suppliers_stock
MODIFY supplier_name VARCHAR(255) NOT NULL UNIQUE;
    
    
DELETE FROM Suppliers_stock;

set sql_safe_updates = 1;
    
    DROP TABLE IF EXISTS Stock;
    
    CREATE TABLE Stock (
    id INT PRIMARY KEY,
    prod_id INT,
    balance_stock INT,
    FOREIGN KEY (prod_id) REFERENCES product_stock(product_id)
    on delete cascade
    on update cascade
);

INSERT INTO Stock (id, prod_id, balance_stock)
VALUES 
       (1, 1001, 50),
       (2, 1002, 75),
       (3, 1003, 100);