SQL Notes

create database Project_3; use Project_3; -- creating table <role> create table role(id int primary key, role_name varchar(100)); -- creating status table create table status (id int primary key,status_name varchar(100),is_user_working bit) -- creating user_account table create table user_account(id int primary key, user_name varchar(100), email varchar(250), password varchar(200), password_salt varchar(50) not null, password_hash_algorithm varchar(50)); --- creating table user_has_role create table user_has_role(id int primary key, role_start_time timestamp, role_end_time datetime not null, user_account_id int foreign key references user_account(id), role_id int foreign key references role(id) ); ----- CREATE TABLE user_has_role (     id INT PRIMARY KEY,     role_start_time TIMESTAMP,     role_end_time DATETIME NOT NULL,     user_account_id INT,     role_id INT,     CONSTRAINT FK_user_account FOREIGN KEY (user_account_id...

create database casestudy; use casestudy; ----------------------------------------------------------------------------------- select top 10 * from fact select top 10 * from location select top 10 * from product ---------------------------------------------------------------------------------- /* Problem Statement:  You are a database administrator. You want to use the data to answer a few  questions about your customers, especially about the sales and profit coming  from different states, money spent in marketing and various other factors such as  COGS (Cost of Goods Sold), budget profit etc. You plan on using these insights  to help find out which items are being sold the most. You have been provided  with the sample of the overall customer data due to privacy issues. But you hope  that these samples are enough for you to write fully functioning SQL queries to  help answer the questions.*/ ---------------------------------------------------------...

create database assignment3; use assignment3; /*You work for a data analytics company, and your client is a food delivery platform similar to Jomato. They have provided you with a dataset containing information about various restaurants in a city. Your task is to analyze this dataset using SQL queries to extract valuable insights and generate reports for your client.*/ -------------------------------------------------------------------------------------------------------------------- -- 1.Create a stored procedure to display the restaurant name, type and cuisine where the table booking is not zero. create procedure sp_zero_booking as select restaurantName,restaurantType,CuisinesType from jomato  where tablebooking ='No' go; exec sp_zero_booking; -------------------------------------------------------------------------------------------------------------- -- 2.Create a transaction and update the cuisine type ‘Cafe’ to ‘Cafeteria’. Check the result and rollback it. --Create a tra...

create database assignment2 use assignment2 select * from jomato -------------------------------------------------------------------------------------------------------- /* Dataset: Jomato  About the dataset:  You work for a data analytics company, and your client is a food delivery platform similar to  Jomato. They have provided you with a dataset containing information about various  restaurants in a city. Your task is to analyze this dataset using SQL queries to extract valuable  insights and generate reports for your client. */ -----------------------------------------------------------------------------------------------------   --Tasks to be performed:  -- 1.Create a user-defined functions to stuff the Chicken into ‘Quick Bites’. Eg: ‘Quick Chicken Bites’  create function fn_modify_restaurant_type(  @oldtype nvarchar(100),  @newtype nvarchar(100)  )  returns table  as  return ( select Orderid,  ...

 create database assignment1; use assignment1; /*Problem Statement:  ABC Fashion is a leading retailer with a vast customer base and a team of dedicated sales  representatives. They have a Sales Order Processing System that helps manage customer  orders and interactions.*/  -- Salesman table creation  CREATE TABLE Salesman (  SalesmanId INT,  Name VARCHAR(255),  Commission DECIMAL(10, 2),  City VARCHAR(255),  Age INT  );  --Salesman table record insertion  INSERT INTO Salesman (SalesmanId, Name, Commission, City, Age)  VALUES  (101, 'Joe', 50, 'California', 17),  (102, 'Simon', 75, 'Texas', 25),  (103, 'Jessie', 105, 'Florida', 35),  (104, 'Danny', 100, 'Texas', 22),  (105, 'Lia', 65, 'New Jersey', 30);  -- Customer table creation  CREATE TABLE Customer (  SalesmanId INT,  CustomerId INT,  CustomerName VARCHAR(255),  PurchaseAmount INT,  );  -- Cust...