@Videonerd03

CREATE TABLE Students( StudentID SMALLINT AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Email VARCHAR(50) UNIQUE, Age SMALLINT CHECK(AGE > 0) ); CREATE TABLE ArtClasses( ClassID SMALLINT AUTO_INCREMENT PRIMARY KEY,

CREATE TABLE Movie ( ID INT UNSIGNED PRIMARY KEY, Title VARCHAR(30), Genre VARCHAR(20), RatingCode VARCHAR(5), Year INT UNSIGNED ); CREATE TABLE YearStats ( Year INT UNSIGNED PRIMARY KEY,

CREATE TABLE MovieCharacter ( CharacterID INT UNSIGNED PRIMARY KEY, Name VARCHAR(30), Franchise VARCHAR(20), FirstAppearanceYear INT UNSIGNED ); CREATE TABLE Movie ( MovieID INT UNSIGNED PRIMARY KEY, Title VARCHAR(80) NOT NULL,

-- The School table will have the following columns: -- SchoolID—positive integer -- SchoolName—variable-length string, maximum 50 characters -- DateConstructed—date -- OperatingBudget—positive decimal value of up to 99.9, with one decimal place -- Classification—fixed-length string with one character -- President—variable-length string, maximum 20 characters

CREATE TABLE Students ( StudentID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(100) NOT NULL, Major VARCHAR(100) NOT NULL ); CREATE TABLE Courses ( CourseID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, Title VARCHAR(100) NOT NULL UNIQUE, Department VARCHAR(50) NOT NULL

-- Customers Table CREATE TABLE Customers ( CustomerID BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), DateOfBirth DATE, HomeCity VARCHAR(100), Email VARCHAR(100) UNIQUE, PhoneNumber VARCHAR(20) UNIQUE, CreatedAt DATETIME

-- Customers Table CREATE TABLE Customers ( CustomerID BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), DateOfBirth DATE, HomeCity VARCHAR(100), Email VARCHAR(100) UNIQUE, PhoneNumber VARCHAR(20) UNIQUE, CreatedAt DATETIME

CREATE TABLE Country ( ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, -- Primary key, auto increment, integer ranging from 0 to 255 CountryName VARCHAR(100), -- Variable-length string with max 100 characters Continent VARCHAR(50), -- Variable-length string with max 50 characters CHECK (Continent IN ( -- Must be one of the listed continents 'Africa', 'Asia', 'Europe',

CREATE TABLE Employee ( id INT PRIMARY KEY, name VARCHAR(100), salary DECIMAL(10,2), bonus DECIMAL(10,2), department VARCHAR(50) ); INSERT INTO Employee (id, name, salary, bonus, department) VALUES

CREATE TABLE Employee( ID INT PRIMARY KEY, Name VARCHAR(50), Salary DECIMAL(10,2), Bonus DECIMAL(8,2), Department VARCHAR(100) ); INSERT INTO(ID, Name, Salary, Bonus, Department)VALUES ('John Doe', 75000.00,5000, 'Engineering'),

CREATE TABLE Passport ( PassportID INT PRIMARY KEY, PassportHolderName VARCHAR(100), Nationality VARCHAR(50), IssueDate DATE, ExpiryDate DATE ); CREATE TABLE Visa ( VisaID INT PRIMARY KEY,

-- Remember to ask the question, are we pulling from both tables(INNER) Or just one table -- Write a query to return the passport holder name and visa type. Unassigned visas should not appear in the results. -- Sort the results by PassportHolderName CREATE TABLE Passport ( PassportID INT PRIMARY KEY, PassportHolderName VARCHAR(100), Nationality VARCHAR(50), IssueDate DATE, ExpiryDate DATE

-- Create tables CREATE TABLE Employee ( ID INT PRIMARY KEY, EmployeeName VARCHAR(50), City VARCHAR(50), DepartmentID INT, Salary DECIMAL(10,2), YearsWithCompany INT, EmployeeManagerID INT, ProjectID INT

-- Write an SQL query to display all visas and the name of the passport holder (if available). Display the VisaID and the Passport Holder Name. Order by VisaID. CREATE TABLE Passport ( PassportID INT UNIQUE PRIMARY KEY, PassportHolderName VARCHAR(100), Nationality VARCHAR(50), IssueDate DATE, ExpiryDate DATE );

-- Write a query to retrieve a list of unique states where students reside, ordered alphabetically from A to Z, from the Student table. CREATE TABLE Student ( ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(30) NOT NULL, Street VARCHAR(50) NOT NULL, City VARCHAR(20) NOT NULL, State CHAR(20) NOT NULL DEFAULT 'TX', Zip MEDIUMINT UNSIGNED NOT NULL,

-- Create the Country Table where: -- ID – Primary Key, Auto Increment, Integer ranging from 0 to 255 -- CountryName – Variable-length string with max 100 characters -- Continent – Variable-length string with max 50 characters; must be one of: -- 'Africa', 'Asia', 'Europe', 'North America', 'South America', 'Oceania', 'Antarctica' -- PopulationMillions – Decimal number with 5 digits total and 1 decimal place; must be between 0.1 and 9999.9

CREATE TABLE Horse ( ID TINYINT AUTO_INCREMENT PRIMARY KEY, RegisteredName VARCHAR(100), Breed CHAR(50), Height DECIMAL (4,1) CHECK (Height BETWEEN 10.0 AND 20.0), -- Height number has 3 significant digits and 1 decimal place must be >= 10.0 and <= 20.0 BirthDate DATE CHECK (BirthDate >= '2015-01-01') -- BirthDate date must be >= Jan 1, 2015 ); CREATE TABLE Student ( ID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- Integer with range 0 to 65 thousand

-- Fill in the blank: An Equi join is a type of that uses the = operatior to match rows -- An Equi join is a type of SQL join where the matching condition is based on equality -- Specifically using the = operator SELECT

-- List all guests and their check-in times (if available) from the guest and Banquet check in tables, -- Check in time from the latest to the earliest. Display the name and check-in time only CREATE TABLE Guest ( GuestCardNumber VARCHAR(20) PRIMARY KEY, Name VARCHAR(20), VIPStatus INT CHECK (VIPStatus IN (0,1)) -- integer 1 = VIP, 0 = Regul ); CREATE TABLE BanquetCheckIn (

CREATE TABLE Employee ( EmployeeCardNumber CHAR(20) PRIMARY KEY, Name VARCHAR(50), Role VARCHAR(10), AccessToThirdFloor INT CHECK(AccessToThirdFloor IN (0,1)), YearsWithShip INT, Salary DECIMAL(10,2) ); CREATE TABLE EmployeeLogTime (