Muhammad al-xorazmiy nomidagi toshkent axborat texnologiyalari univesiteti

O’ZBEKISTON RESPUBLIKASI AXBORAT TEXNOLOGIYALARI VA KOMMUNIKATSIYALARINI RIVOJLANTIRISHVAZIRLIGI
MUHAMMAD AL-XORAZMIY NOMIDAGI TOSHKENT AXBORAT TEXNOLOGIYALARI UNIVESITETI

Ma’lumotlar bazasi

1-2- Amaliy mashg'ulot

Bajardi: Ismoilov Iskandar

Tekshirdi: URALOVA I. A.
Toshkent 2023


Kasb hunar kolleji malumotlar bazasini yaratish

Birinchi navbatda mysql terminalida DATABASE yaratamiz.Buning uchun

avval mysql serveriga kirib olamiz mysql -u root -p terminal kodi orqali kiramiz va passwordni kiritamiz.

Keyin esa o’zimiz uchun DATABASE yaratib unga nom beramiz

Endi yaratgan bazamizdan foydalanish uchun USE kalit so’zidan foydalanamiz.

DATABASE ga ulanib oldik endi kerakli tablitsalarni yaratib olamiz.

Birinchi navbatda Departments nomli jadvalni yaratamiz.Buning uchun
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(255) NOT NULL,
DepartmentHead VARCHAR(255),
UNIQUE (DepartmentName)
);
Query dan foydalamiz.

Shu ketma-ketlikda malumotlarni qo’shib chiqamiz.

CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(255) NOT NULL,
Credits INT,
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID),
UNIQUE (CourseName)
);

CREATE TABLE Students (

StudentID INT PRIMARY KEY,
FirstName VARCHAR(255) NOT NULL,
LastName VARCHAR(255) NOT NULL,
DateOfBirth DATE,
Gender ENUM('Male', 'Female', 'Other'),
ContactInfo VARCHAR(255),
UNIQUE (FirstName, LastName)
);

CREATE TABLE Faculty (

FacultyID INT PRIMARY KEY,
FirstName VARCHAR(255) NOT NULL,
LastName VARCHAR(255) NOT NULL,
DepartmentID INT,
ContactInfo VARCHAR(255),
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID),
UNIQUE (FirstName, LastName)
);

CREATE TABLE Classrooms (

ClassroomID INT PRIMARY KEY,
RoomNumber VARCHAR(20) NOT NULL,
Capacity INT,
Building VARCHAR(255),
UNIQUE (RoomNumber)
);


CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
Semester VARCHAR(20),
Grade VARCHAR(5),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

CREATE TABLE Schedule (

ScheduleID INT PRIMARY KEY,
CourseID INT,
FacultyID INT,
ClassroomID INT,
DaYofWeyek VARCHAR(10),
TimeSlot VARCHAR(20),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID),
FOREIGN KEY (FacultyID) REFERENCES Faculty(FacultyID),
FOREIGN KEY (ClassroomID) REFERENCES Classrooms(ClassroomID)
);

Bizga kerakli barcha jadvallarni yaratib oldik endi ularga ma’lumotlarni qo’shib olamiz

INSERT INTO Departments (DepartmentID, DepartmentName, DepartmentHead) VALUES
(1, 'Computer Sciyence', 'Dr. Smith'),
(2, 'Business Administration', 'Prof. Johnson'),
(3, 'Electrical Engineyering‘, 'Dr. Brown');

INSERT INTO Courses (CourseID, CourseName, Credits, DepartmentID) VALUES

(101, 'Introduction to Programming‘, 3, 1),
(102, 'Database Management', 4, 1),
(201, 'Principles of Marketing‘, 3, 2),
(202, 'Financial Accounting‘, 4, 2),
(301, 'Circuit Analysis', 4, 3);

INSERT INTO Students (StudentID, FirstName, LastName, DateOfBirth, Gender, ContactInfo) VALUES

(1001, 'John', 'Doe', '1995-05-15', 'Male', '[e-mail]'),
(1002, 'Jane', 'Smith', '1996-08-22', 'Female', '[e-mail]'),
(1003, 'Robert', 'Johnson', '1997-03-10', 'Male', '[e-mail]');

INSERT INTO Faculty (FacultyID, FirstName, LastName, DepartmentID, ContactInfo) VALUES

(2001, 'Prof', 'Brown', 1, '[e-mail]'),
(2002, 'Dr', 'White', 2, '[e-mail]'),
(2003, 'Prof', 'Green', 3, '[e-mail]');

INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID, Semester, Grade) VALUES

(5001, 1001, 101, 'Fall 2022', 'A'),
(5002, 1001, 102, 'Fall 2022', 'B'),
(5003, 1002, 201, 'Fall 2022', 'A-'),
(5004, 1002, 202, 'Fall 2022', 'B+'),
(5005, 1003, 301, 'Fall 2022', 'A');

SELECT Students.FirstName, Students.LastName, Enrollments.Grade

FROM Students
JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
JOIN Courses ON Enrollments.CourseID = Courses.CourseID
WHERE Courses.CourseName = 'Introduction to Programming' AND Enrollments.Grade > 'B';


SELECT DISTINCT Students.FirstName, Students.LastName
FROM Students
JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
JOIN Courses ON Enrollments.CourseID = Courses.CourseID
WHERE Courses.CourseName = 'Introduction to Programming' OR Courses.CourseName = 'Financial Accounting';

SELECT Students.FirstName, Students.LastName

FROM Students
WHERE Students.StudentID NOT IN (SELECT DISTINCT StudentID FROM Enrollments);