@Videonerd03

-- Retrieve all students with their program names, including students without programs -- Return the student name( as Student_Name) and the program name(As Program_Name) CREATE TABLE Student( ID MEDIUMINT UNSIGNED PRIMARY KEY, Name VARCHAR(150), Age INT, Program_ID INT, FOREIGN KEY(Program_ID) REFERENCES Program(ID)

-- Update the program_ID to 5 for student with ID = 101 UPDATE Student SET Program_ID = 5 WHERE ID = 101; -- Retrieve students older then 20 enrolled in program 102 SELECT ID, Name, Age, Program_ID FROM Student WHERE Age > 20 AND Program_ID = 102;

-- Alter Table, Add column -- Add a column named Head_Of_Department ot the table. It is a variable length string with up to 100 to store the department head's name ALTER TABLE Department ADD COLUMN Head_Of_Department VARCHAR(100); -- Modify the Office_Location column in the department table to increase its limit from 50 to 100 characters ALTER TABLE Department MODIFY COLUMN Office_Location VARCHAR(100);

-- TINYINT -- Signed -128 to 126 -- Unsigned 0 to 255 -- SMALLINT -- Signed -32,768 to 32,767 -- Unsigned 0 to 65,535 -- MEDIUMINT -- Signed -8,388,608 to 8,388,607

-- Which of the following is not a valid SQL Statement -- DELETE TABLE employees WHERE id = 1; -- Removes rows from the table based on a condition -- If you want to delete a specific row -- Use DELETE FROM -- If you want to remove the whole table -- DROP TABLE -- DROP VIEW DROP VIEW student_courses;

-- Data Definition Language (DDL) -- Subset of SQL used to define and manage the structure of a database (Schmeas, tables, indexes) -- Examples -- CREATE - Create a new table or database -- ALTER - Modify the structure of an existing table -- DROP - Delete a table or database -- Purpose: Deals with schema-level changes, not the actual data inside the tables

-- 1. Create the Departments Table (without foreign key constraints) CREATE TABLE Departments ( ID INT PRIMARY KEY, DepartmentName VARCHAR(50) NOT NULL, MainCity VARCHAR(50), DepartmentManagerID INT ); -- 2. Create the Projects Table (with TimeRemaining as INT in days) CREATE TABLE Projects (

-- 1. Create the Departments Table (without foreign key constraints) CREATE TABLE Departments ( ID INT PRIMARY KEY, DepartmentName VARCHAR(50) NOT NULL, MainCity VARCHAR(50), DepartmentManagerID INT ); -- 2. Create the Projects Table (with TimeRemaining as INT in days) CREATE TABLE Projects (

CREATE TABLE Departments ( ID INT PRIMARY KEY, DepartmentName VARCHAR(50) NOT NULL, MainCity VARCHAR(50), DepartmentManagerID INT ); -- 2. Create the Projects Table (with TimeRemaining as INT in days) CREATE TABLE Projects ( ID INT PRIMARY KEY,

-- 1. Create the Departments Table (without foreign key constraints) CREATE TABLE Departments ( ID INT PRIMARY KEY, DepartmentName VARCHAR(50) NOT NULL, MainCity VARCHAR(50), DepartmentManagerID INT ); -- 2. Create the Projects Table (with TimeRemaining as INT in days) CREATE TABLE Projects (

-- You have two tables, Table A and Table B. Which of the following correctly describes the result set -- of a CROSS JOIN compared to a FULL JOIN -- A CROSS JOIN returns all combinations of rows from both tables (Every possible combination) -- While a FULL JOIN returns all matching and non matching rows from both tables (LEFT, RIGHT JOIN) -- Cross join is a type of join that returns the cartesian product of two tables. -- Means every row from the first table is combined with every row from

-- Outer self join is a type of SQL join where a table is joined to itself using an outer join -- LEFT, RIGHT OR FULL JOIN -- Usefull when you want to compare rows within the same table and still include rows that dont have a match CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name VARCHAR(50), ManagerID INT );

-- Create Characters table CREATE TABLE Characters ( CharacterID INT AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(100) NOT NULL, Alias VARCHAR(100), FirstAppearance DATE NOT NULL ); -- Insert sample data into Characters INSERT INTO Characters (Name, Alias, FirstAppearance) VALUES

CREATE TABLE Departments ( DepartmentID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, DepartmentName VARCHAR(50) NOT NULL ); CREATE TABLE Employees ( EmployeeID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, DepartmentID INT NOT NULL,

-- An outer join is where a table is joined to itself using an -- outer join(typically LEFT or RIGHT OUTER JOIN) -- Useful when you want to compare rows within the same table -- Still include rows that dont have a join -- PRACTICE OUTER JOINS

-- Show the total number of employees in the Employees Table SELECT COUNT(*) FROM Employees; --Find the average salary(Average Salary) -- Of Employees in the employees table SELECT AVG(Salary) AS AverageSalary FROM Employees;

-- TABLE CREATION CREATE TABLE Top100Films ( FilmID INT AUTO_INCREMENT PRIMARY KEY, Title VARCHAR(255) NOT NULL, ReleaseYear INT, DurationMinutes INT, Rating DECIMAL(3,1) NOT NULL, MetaScore INT NOT NULL, Director VARCHAR(255), MainActors TEXT,

-- Create Customers table CREATE TABLE Customers ( CustomerID INT AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(100), CreditLimit DECIMAL(10,2) NOT NULL ); -- Create Orders table CREATE TABLE Orders ( OrderID INT AUTO_INCREMENT PRIMARY KEY,

CREATE TABLE Projects( ProjectID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, Title VARCHAR(100) NOT NULL, Type ENUM('Movie','TV Show', 'Podcast') NOT NULL, ReleaseYear SMALLINT UNSIGNED, Notes TEXT ); CREATE TABLE Roles ( RoleID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,

CREATE TABLE Actors ( ActorID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ActorName VARCHAR(30), CharacterName VARCHAR(30), BirthDate SMALLINT NOT NULL, Nationality VARCHAR(30) ); CREATE TABLE Directors (