SQL Code Snippets

CMD command

   CD C:\xampp\mysql\bin  

press enter button

   mysql.exe -u root -p 

press again enter button

If there's no password, just press Enter again

Create database

  create database dbuniversity;         

Show databases

  show databases;    

Use database

  use dbuniversity;  

create table university

  CREATE TABLE university (
    ID VARCHAR(10) PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    ChargePerTerm DECIMAL(10, 2),
    NumOfTerms INT(3) NOT NULL
);  

Desc university

 desc university;  

Create table Student

 CREATE TABLE Student (
    UniID VARCHAR(10),
    IndexNo VARCHAR(10) NOT NULL,
    Name VARCHAR(50) NOT NULL,
    DateJoined DATE NOT NULL,
    PRIMARY KEY (UniID, IndexNo),
    FOREIGN KEY (UniID) REFERENCES University(ID)
); 

Describe Student

  desc Student; 

Insert into university

 INSERT INTO university
VALUES
    ('U001', 'University of Technology', 50000.00, 2),
    ('U002', 'University of AppliedScience', 12000.00, 3); 

Insert into Student

      INSERT INTO Student
VALUES
('U001', 'ST00001', 'Tommy', '1995/06/05'),
('U001', 'ST00002', 'Linda', '1995/06/22'),
('U002', 'ST00003', 'Mathives', '1995/06/30'),
('U002', 'ST00001', 'Tommy', '1995/06/30');     

Select all from university

  select * from university;      

Select all from student

 select * from Student;       

Select name from student

 select Name from Student;       

Select name as "Undergraduates" from students

  select Name as "Undergraduates"
          from Student;      

Select indexno, name from students

  select indexno, Name from Student;      

Select indexno, name as "Undergraduates" from students

   select indexno, Name as "Undergraduates" 
          from Students;     

Select indexno, name as "Undergraduates" from students Order By indexno Desc

    select indexno, Name as "Undergraduates"
          from Student
          Order By indexno Desc;    

Select indexno, name as "Undergraduates" from students Order By name Desc

   select indexno, Name as "Undergraduates" 
          from Student
          Order By name Desc;    

Select distinct name from students

  Select distinct name from Student;   

ALTER table university

  ALTER table university 
        ADD column
        Payment double(10,2);
        
        
        

Desc university

  desc university;  

Select all from university

     select * from university;     

Update university

    UPDATE university   
        SET Payment = (  ChargePerTerm * NumOfTerms  )+2500;
        
        

Select all from university

  select * from university;   

Select payment from university where name like 'university of technology'

  select payment
          from university 
          where Name like 'University of Technology';   

Select payment all 1000 as "Income" from university where name like 'university of technology'

  select payment * 1000 as "Income"
          from university
          where Name like 'University of Technology';  

Select count (IndexNo) from studen where uniid like 'UOO1'

  select count (IndexNo)
          from Student
          where uniid LIKE 'UOO1'