Notes

show databases;

create table EMP(
EmpNo varchar(20),
Ename varchar(20),
Job varchar(20),
DOJ date
);

show tables;

alter table emp add mobileno numeric(10);

desc emp;
alter table emp rename column DOJ to DOB;
desc emp;

insert into emp(EmpNo,Ename,
Job,DOB ,mobileno)
values
(1,'Ankita','AP','2023-6-2',7865949340),
(2,'xyz','AP','2023-6-9',786567534),
(3,'fhjk','AP','2023-9-4',942549340);

desc emp;

select * from emp;

select DOB from emp
truncate table emp;
select * from emp;

show databases;

create table Student(
id varchar(20) not null,
name varchar(20),
age integer,
gpa real,
primary key(id));

create table Enrolled(
id varchar(20) not null,
did varchar(20),
hod varchar(20),
primary key(id,did),
FOREIGN KEY(id) references Student(id)
);

desc Enrolled;

insert into Student values('53688','smith',18,3.2),('53650','Raven',19,3.8),
('53666','Joe',18,3.2);

select * from Student;

insert into Student values('53688','Joe',18,3.2)

insert into Enrolled values('53666','cs','A'),('53666','ds','B'),
('53688','ec','C'),('53650','me','D');

select * from Enrolled;

insert into Enrolled values('53669','CS','A');

insert into Student values('53669','Jane',17,3.1);

delete from Student where name='jane';

delete from Enrolled where id='53688';
select * from student ;

create table EmpSalary
(
id int Primary Key,
Empname varchar(100),
Department varchar(100),
Category varchar(5),
Salary INT
);
Insert into EmpSalary(id,EmpName,Department,Category,Salary) VALUES
(1,'suresh','management','A',800000),
(2,'Dinesh','IT','A',750000),
(3,'Ramesh','CSE','A',600000),
(4,'Bhavesh','management','B',400000),
(5,'RaviKr','IT','B',300000),
(6,'Arhan Mansoori','IT','B',800000),
(7,'suresh','management','B',800000),
(8,'RangKar','CS','C',150000),
(9,'Priya Devi','IT','C',130000);

CREATE TABLE CountryDIM (
CountryID INT PRIMARY KEY,
CountryName VARCHAR(50)
);





INSERT INTO CountryDIM (CountryID, CountryName)
VALUES
(1, 'USA'),
(2, 'UK'),
(3, 'Canada');

select * from CountryDIM;

CREATE TABLE CourseDIM (
CourseCode VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(55),
Duration INT,
CourseLevel VARCHAR(50)
);

INSERT INTO CourseDIM (CourseCode, CourseName, Duration, CourseLevel)
VALUES
('CSE101', 'Computer Science 101', 4, 'Undergraduate'),
('ENG201', 'English Literature', 3, 'Undergraduate'),
('BUS301', 'Business Management', 2, 'Undergraduate')

select * from CourseDIM;

CREATE TABLE StudentDIM (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(255),
StudentAddress VARCHAR(255),
StudentPhone VARCHAR(20)
);

INSERT INTO StudentDIM (StudentID, StudentName, StudentAddress, StudentPhone)
VALUES
(101, 'John Smith', '123 Main St, Anytown, USA', '+1-555-123-4567'),
(102, 'Emma Johnson', '456 Oak St, Another Town, USA', '+1-555-987-6543'),
(103, 'Michael Brown', '789 Elm St, Some City, USA', '+1-555-321-0987');

select * from StudentDIM;

CREATE TABLE YearDIM (
EnrollmentYear INT PRIMARY KEY
);

INSERT INTO YearDIM (EnrollmentYear)
VALUES
(2022),
(2023),
(2024);

select * from YearDIM;

CREATE TABLE CollegeFact (
FactID INT PRIMARY KEY,
CountryID INT,
CourseCode VARCHAR(10),
StudentID INT,
EnrollmentYear INT,

FOREIGN KEY (CountryID) REFERENCES CountryDIM(CountryID),
FOREIGN KEY (CourseCode) REFERENCES CourseDIM(CourseCode),
FOREIGN KEY (StudentID) REFERENCES StudentDIM(StudentID),
FOREIGN KEY (EnrollmentYear) REFERENCES YearDIM(EnrollmentYear)
);

INSERT INTO CollegeFact (FactID, CountryID, CourseCode, StudentID, EnrollmentYear)
VALUES
(1, 1, 'CSE101', 101, 2022),
(2, 2, 'ENG201', 102, 2023),
(3, 3, 'BUS301', 103, 2024);

select * from CollegeFact;

select * from CollegeFact where EnrollmentYear = 2024;

select * from CountryDIM INNER JOIN
CourseDIM on CountryDIM.CountryID=CourseDIM.CourseCode;


select * from CountryDIM LEFT OUTER JOIN
CourseDIM on
CountryDIM.CountryID=CourseDIM.CourseCode;

select * from CountryDIM RIGHT OUTER JOIN
CourseDIM on
CountryDIM.CountryID=CourseDIM.CourseCode;

select * from CountryDIM FULL OUTER JOIN
CourseDIM on
CountryDIM.CountryID=CourseDIM.CourseCode;

select * from CountryDIM INNER JOIN
CourseDIM on CountryDIM.CountryID=CourseDIM.CourseCode
INNER JOIN StudentDIM ON CourseDIM.CourseCode= StudentDIM.StudentID
LEFT OUTER JOIN YearDIM ON StudentDIM.StudentID= YearDIM.EnrollmentYear;


select * from CollegeFact