Scribd
Upload
5 views2 pages

Oracle Assignment

The document outlines an Oracle assignment by Said Mohamed Omar, which includes the creation of two tables, Students and Courses, along with data insertion. It also provides various SQL queries demonstrating different types of joins, aggregate functions, grouping, and conditional selections using NVL, BETWEEN, IN, and LIKE. The queries aim to extract and manipulate data related to students and their enrolled courses.

Uploaded by

saciidmaxamed043
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views2 pages

Oracle Assignment

The document outlines an Oracle assignment by Said Mohamed Omar, which includes the creation of two tables, Students and Courses, along with data insertion. It also provides various SQL queries demonstrating different types of joins, aggregate functions, grouping, and conditional selections using NVL, BETWEEN, IN, and LIKE. The queries aim to extract and manipulate data related to students and their enrolled courses.

Uploaded by

saciidmaxamed043
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

ORACLE ASSIGNMENT

NAME: Said Mohamed Omar


1. TABLES :
CREATE TABLE Students ( Student_ID NUMBER PRIMARY KEY, Student_Name
VARCHAR2(50),Gender VARCHAR2(10) );
CREATE TABLE Courses (Course_ID NUMBER PRIMARY KEY, Course_Name
VARCHAR2(50),Fee NUMBER, Start_Date DATE, Student_ID NUMBER, CONSTRAINT
fk_student FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID) );
INSERT DATA
INSERT INTO Students VALUES (1, 'Ali', 'Male');
INSERT INTO Students VALUES (2, 'Ayan', 'Female');
INSERT INTO Students VALUES (3, 'Mohamed', 'Male');

INSERT INTO Courses VALUES (101, 'Database', 200, DATE '2023-01-10', 1);
INSERT INTO Courses VALUES (102, 'Web Design', 250, DATE '2023-03-15', 1);
INSERT INTO Courses VALUES (103, 'Networking', 180, DATE '2023-06-01', 2);
INSERT INTO Courses VALUES (104, 'Programming', NULL, DATE '2023-09-20', NULL);

1. 4 QUERISE { INNER, LEFT, RIGHT, FULL }


-- INNER JOIN
SELECT s.Student_Name, c.Course_Name
FROM Students s
INNER JOIN Courses c
ON s.Student_ID = c.Student_ID;
-- LEFT JOIN
SELECT s.Student_Name, c.Course_Name
FROM Students s
LEFT JOIN Courses c
ON s.Student_ID = c.Student_ID;
-- RIGHT JOIN
SELECT s.Student_Name, c.Course_Name
FROM Students s
RIGHT JOIN Courses c
ON s.Student_ID = c.Student_ID;
-- FULL JOIN
SELECT s.Student_Name, c.Course_Name
FROM Students s
FULL JOIN Courses c
ON s.Student_ID = c.Student_ID;

2. 5 QUERIES FROM AGGEGATE FUNCTIONS


-- MIN
SELECT MIN(Fee) AS Min_Fee
FROM Courses;
-- MAX
SELECT MAX(Fee) AS Max_Fee
FROM Courses;
-- SUM
SELECT SUM(Fee) AS Total_Fee
FROM Courses;
-- AVG
SELECT AVG(Fee) AS Avg_Fee
FROM Courses;
-- COUNT
SELECT COUNT(*) AS Total_Courses
FROM Courses;
3. ONE QUERY FROM GROUP BY
SELECT Student_ID, COUNT(*) AS Course_Count
FROM Courses
GROUP BY Student_ID;
4. USING NVL FOR A QUERY
SELECT Course_Name, NVL(Fee, 0) AS Fee
FROM Courses;
5. 3 DIFERENT QUERIES FROM { BETWEEN, IN, LIKE }
-- BETWEEN
SELECT Course_Name, Fee
FROM Courses
WHERE Fee BETWEEN 180 AND 250;
-- IN
SELECT Course_Name
FROM Courses
WHERE Student_ID IN (1, 2);
-- LIKE
SELECT Student_Name
FROM Students
WHERE Student_Name LIKE 'A%';

You might also like