SQLASSIGNMENT1.sql

CREATE DATABASE SQL_Assignment1;

use SQL_Assignment1;

--- 1) Return your name
SELECT 'Anjalii Kolambkar' AS Name;

--- 2) Product of 7 and 4
SELECT 7 * 4 AS Product;

--- 3) (7 - 4) * 8
SELECT (7 - 4) * 8 AS Result;

--- 4) Brewster’s SQL Training Class
SELECT 'Brewster''s SQL Training Class' AS TrainingName;

--- 5) Phrase and calculation in two columns
SELECT 'Day 1 of Training' AS TrainingDay,
       5 * 3 AS Result;


CREATE DATABASE SQL_Assignmentpart2;

use SQL_Assignmentpart2;

-----CREATING TABLE ------------------------------------------------------------------------------------------

-- Employee table
CREATE TABLE Employee
(
    EID INT,
    Ename VARCHAR(20),
    Eage INT,
    Esalary MONEY,
    JobTitle VARCHAR(50),
    BirthDate DATE
);

-- Customer table
CREATE TABLE Customer
(
    CID INT,
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    Email VARCHAR(50)
)
-- Product table
CREATE TABLE Product
(
    PID INT,
    Name VARCHAR(50),
    Price MONEY
);
-- Department table
CREATE TABLE Department
(
    DID INT,
    DName VARCHAR(30),
    GroupName VARCHAR(30)
);
-- SalesOrder table
CREATE TABLE SalesOrder
(
    OrderID INT,
    CID INT,
    OrderDate DATE,
    TotalAmount MONEY
);
-- BillOfMaterials table
CREATE TABLE BillOfMaterials
(
    BOMID INT,
    ProductID INT,
    ComponentID INT,
    Quantity INT
);
-- PersonDemographics table
CREATE TABLE PersonDemographics
(
    PersonID INT,
    TotalPurchase MONEY,
    YearlyIncome MONEY,
    Education VARCHAR(30)
);


-------QUESTIONS--------------------------------------------------------------------------------------
------ 1) Select Employee ID only
SELECT EID
FROM Employee;

----- 2) Select Employee ID and Job Title
SELECT EID, JobTitle
FROM Employee;

----- 3) Top 20 percent Employee rows
SELECT TOP 20 PERCENT
       EID AS NationalIDNumber,
       JobTitle,
       BirthDate
FROM Employee;


---- 4) Top 500 Employee rows with alias
SELECT TOP 500
       EID AS [Employee ID],
       Ename AS [Employee Name],
       JobTitle AS [Job Title],
       BirthDate
FROM Employee;

----- 5) All SalesOrder rows
SELECT *
FROM SalesOrder;

---- 6) Top 50 percent Customers
SELECT TOP 50 PERCENT *
FROM Customer;

---- 7) Product name with alias
SELECT Name AS [Product's Name]
FROM Product;

---- 8) Top 400 Departments
SELECT TOP 400 *
FROM Department;

--- 9) All BillOfMaterials rows
SELECT *
FROM BillOfMaterials;

---- 10) Top 1500 PersonDemographics rows
SELECT TOP 1500 *
FROM PersonDemographics;