Paste your fonts here !!!

[nikhilrajganta]

[nikhilrajganta]

[ragavkumarv]

[srujan1306]

[Nithin1297]

[Manideep-1295]

[Abhaymanhas24]

[nehapatlolla]

[Pavan-kolla3]

[vaishu-gundlapally]

[ragavkumarv]

Task

Content

Intended Audience This tutorial assumes that you already have a static React site (Gatsby/Next) being deployed on Vercel or Netlify.

It's written with the intermediate React developer in mind; if you're newer to the modern JS ecosystem, it might be a bit hard to follow.

[shreyap-23]

[varunmunipelli]

[srini074]

[AishuPola]

[Tejasanala]

[Rishisairi]

[Hemasri-P]

[ragavkumarv]

Content

Beast is an upcoming Indian Tamil-language action thriller film written and directed by Nelson and produced by Sun Pictures. The film stars Vijay and Pooja Hegde, while Selvaraghavan, Yogi Babu and Redin Kingsley play supporting roles

Centering

 body {
        margin: 0;
        min-height: 100vh;
        display: grid;
        place-content: center;
      }

[nikhilrajganta]

[ragavkumarv]

CREATE TABLE salesman (
    salesman_id INT PRIMARY KEY,
    name VARCHAR(255),
    city VARCHAR(255),
    commission DECIMAL(4, 2)
);

Select * from salesman

INSERT INTO salesman (salesman_id, name, city, commission) VALUES
(5001, 'James Hoog', 'New York', 0.15), -- Print
(5002, 'Nail Knite', 'Paris', 0.13),
(5005, 'Pit Alex', 'London', 0.11), -- Print
(5006, 'Mc Lyon', 'Paris', 0.14), -- Print
(5003, 'Lauson Hen', NULL, 0.12),
(5007, 'Paul Adam', 'Rome', 0.13); -- Print

Task 1

Find the average commision of a saleman from Paris

Task 2

Find out if there are cities with only one salesman and list them | No nulls Clue: Having

CREATE TABLE orders (
    ord_no INT PRIMARY KEY,
    purch_amt DECIMAL(10, 2),
    ord_date DATE,
    customer_id INT,
    salesman_id INT
);

INSERT INTO orders (ord_no, purch_amt, ord_date, customer_id, salesman_id) VALUES
(70001, 150.5, '2012-10-05', 3005, 5002),
(70009, 270.65, '2012-09-10', 3001, 5005),
(70002, 65.26, '2012-10-05', 3002, 5001),
(70004, 110.5, '2012-08-17', 3009, 5003),
(70007, 948.5, '2012-09-10', 3005, 5002),
(70005, 2400.6, '2012-07-27', 3007, 5001),
(70008, 5760, '2012-09-10', 3002, 5001),
(70010, 1983.43, '2012-10-10', 3004, 5006),
(70003, 2480.4, '2012-10-10', 3009, 5003),
(70012, 250.45, '2012-06-27', 3008, 5002),
(70011, 75.29, '2012-08-17', 3003, 5007),
(70013, 3045.6, '2012-04-25', 3002, 5001);

Select * from orders;

-- Task 3 - Sub-Query -- Write a query to display all the orders from the orders table issued by the salesman 'Paul Adam'.

-- Task 4 -- Write a query to display all the orders which values are greater than the average order value for 10th October 2012

-- Task 5 (Challenging) -- Write a query to find all orders with order amounts which are above-average amounts for their customers.

-- Task 6
-- Write a query to find all orders attributed to a salesman in 'Paris' -- Clue: In operator

CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    cust_name VARCHAR(255),
    city VARCHAR(255),
    grade INT NULL,
    salesman_id INT
);
INSERT INTO customer (customer_id, cust_name, city, grade, salesman_id) VALUES
(3002, 'Nick Rimando', 'New York', 100, 5001),
(3005, 'Graham Zusi', 'California', 200, 5002),
(3001, 'Brad Guzan', 'London', NULL, 5005),
(3004, 'Fabian Johns', 'Paris', 300, 5006),
(3007, 'Brad Davis', 'New York', 200, 5001),
(3009, 'Geoff Camero', 'Berlin', 100, 5003),
(3008, 'Julian Green', 'London', 300, 5002),
(3003, 'Jozy Altidor', 'Moscow', 200, 5007);

-- Task 7 -- Write a query to find the name and id of all salesmen who had more than one customer

[ragavkumarv]

Tasks on functions

Example Datasets

Employees Table

EmployeeID	FirstName	LastName
1	John	Doe
2	Jane	Smith
3	Alice	Johnson

Products Table

ProductID	ProductName	ProductDescription
101	Widget A	A standard widget
102	Gadget B	A fancy new gadget
103	Thingamajig	A very useful tool

SalesOrders Table

SalesOrderID	OrderDate	TotalAmount
1001	2023-01-15 14:33:00	150.00
1002	2023-03-22 10:45:00	200.00
1003	2024-05-17 09:20:00	350.00

Customers Table

CustomerID	CustomerName	Address
201	Acme Corp	123 Main St
202	Globex Inc	456 Elm St
203	Initech	789 Oak St

Exercise Scenarios and Expected Output

Exercise 1: Employee Full Name

Scenario: You need to create a list of employees with their full names in uppercase.

Expected Output:	EmployeeID	FullName
1	JOHN DOE
2	JANE SMITH
3	ALICE JOHNSON

Exercise 2: Product Description Length

Scenario: You need to find the length of the product descriptions.

Expected Output:	ProductID	ProductName	DescriptionLength
101	Widget A	17
102	Gadget B	18
103	Thingamajig	18

Exercise 3: Sales Date Formatting

Scenario: You need to display sales order dates in the YYYY-MM-DD format.

Expected Output:	SalesOrderID	FormattedOrderDate
1001	2023-01-15
1002	2023-03-22
1003	2024-05-17

Exercise 4: Customer Address Cleanup

Scenario: You need to remove leading and trailing spaces from the customer addresses.

Expected Output:	CustomerID	CustomerName	CleanedAddress
201	Acme Corp	123 Main St
202	Globex Inc	456 Elm St
203	Initech	789 Oak St

Exercise 5: Sales Summary by Year

Scenario: You need to show the total sales amount for each year.

Expected Output:	Year	TotalSales
2023	350.00
2024	350.00

Exercise 1: Employee Full Name and Department

Scenario: Create a list of employees with their full names in uppercase and their department names. Ensure the full name is in uppercase and the department name is in lowercase.

Tables:

• Employees (EmployeeID, FirstName, LastName, DepartmentID)
• Departments (DepartmentID, DepartmentName)

Expected Result:

• EmployeeID, FullName, DepartmentName

Example Data:

Employees Table

EmployeeID	FirstName	LastName	DepartmentID
1	John	Doe	101
2	Jane	Smith	102
3	Alice	Johnson	103

Departments Table

DepartmentID	DepartmentName
101	Sales
102	Engineering
103	Marketing

Expected Output:	EmployeeID	FullName	DepartmentName
1	JOHN DOE	sales
2	JANE SMITH	engineering
3	ALICE JOHNSON	marketing

Exercise 2: Product Description Analysis

Scenario: Find the products where the description length is more than 15 characters. Additionally, reverse the product descriptions and show the first 10 characters of the reversed description.

Tables:

• Products (ProductID, ProductName, ProductDescription)

Expected Result:

• ProductID, ProductName, DescriptionLength, ReversedDescription

Example Data:

Products Table

ProductID	ProductName	ProductDescription
101	Widget A	A standard widget
102	Gadget B	A fancy new gadget
103	Thingamajig	A very useful tool

Expected Output:	ProductID	ProductName	DescriptionLength	ReversedDescription
101	Widget A	17	tegdiw dradnatS
102	Gadget B	18	te dgan ycnA
103	Thingamajig	18	loot lufesY

Exercise 3: Sales Analysis by Customer and Year

Scenario: Write a query to display the total sales amount for each customer for each year. Additionally, include the customer's name with trailing spaces removed and format the total sales amount to two decimal places.

Tables:

• SalesOrders (SalesOrderID, CustomerID, OrderDate, TotalAmount)
• Customers (CustomerID, CustomerName)

Expected Result:

• CustomerID, CustomerName, Year, TotalSales

Example Data:

SalesOrders Table

SalesOrderID	CustomerID	OrderDate	TotalAmount
1001	201	2023-01-15 14:33:00	150.00
1002	202	2023-03-22 10:45:00	200.00
1003	201	2024-05-17 09:20:00	350.00

Customers Table

CustomerID	CustomerName
201	Acme Corp
202	Globex Inc
203	Initech

Expected Output:	CustomerID	CustomerName	Year	TotalSales
201	Acme Corp	2023	150.00
201	Acme Corp	2024	350.00
202	Globex Inc	2023	200.00

Exercise 4: Enhanced Product Search

Scenario: Write a query to find products where the description contains the word "useful" and replace the word "useful" with "beneficial". Return the product ID, product name, and modified description.

Tables:

• Products (ProductID, ProductName, ProductDescription)

Expected Result:

• ProductID, ProductName, ModifiedDescription

Example Data:

Products Table

ProductID	ProductName	ProductDescription
101	Widget A	A standard widget
102	Gadget B	A fancy new gadget
103	Thingamajig	A very useful tool

Expected Output:	ProductID	ProductName	ModifiedDescription
103	Thingamajig	A very beneficial tool

Exercise 5: Detailed Sales Report

Scenario: Write a query to generate a detailed sales report. Include the sales order ID, customer name (without leading or trailing spaces), formatted order date (YYYY-MM-DD), and the total amount formatted to two decimal places.

Tables:

• SalesOrders (SalesOrderID, CustomerID, OrderDate, TotalAmount)
• Customers (CustomerID, CustomerName)

Expected Result:

• SalesOrderID, CustomerName, FormattedOrderDate, TotalAmount

Example Data:

SalesOrders Table

SalesOrderID	CustomerID	OrderDate	TotalAmount
1001	201	2023-01-15 14:33:00	150.00
1002	202	2023-03-22 10:45:00	200.00
1003	201	2024-05-17 09:20:00	350.00

Customers Table

CustomerID	CustomerName
201	Acme Corp
202	Globex Inc
203	Initech

Expected Output:	SalesOrderID	CustomerName	FormattedOrderDate	TotalAmount
1001	Acme Corp	2023-01-15	150.00
1002	Globex Inc	2023-03-22	200.00
1003	Acme Corp	2024-05-17	350.00

[ragavkumarv]

Setup for All, Any, Exists

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL,
    department_id INT,
    manager_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id),
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

INSERT INTO departments (department_id, department_name) VALUES
(1, 'Sales'),
(2, 'HR'),
(3, 'IT'),
(4, 'Finance');

INSERT INTO employees (employee_id, first_name, last_name, salary, department_id, manager_id) VALUES
(1, 'John', 'Doe', 60000, 1, NULL),
(2, 'Jane', 'Smith', 75000, 1, 1),
(3, 'Emily', 'Jones', 50000, 2, NULL),
(4, 'Michael', 'Brown', 55000, 2, 3),
(5, 'Chris', 'Wilson', 95000, 3, NULL),
(6, 'Sarah', 'Taylor', 40000, 3, 5),
(7, 'David', 'Lee', 105000, 3, 5),
(8, 'Paul', 'Walker', 120000, 4, NULL),
(9, 'Laura', 'Hall', 110000, 4, 8);

-- Task -- Write a query to display only those customers whose grade are, in fact, higher than every customer in New York.

-- Task -- Write a query to find all orders with an amount (purch_amt) smaller than any amount for a customer in London.

[ragavkumarv]

Setup for Set operations

CREATE TABLE Employees (
    EmployeeID INT,
    Name NVARCHAR(50),
    Department NVARCHAR(50),
    Role NVARCHAR(50)
);

CREATE TABLE Projects (
    ProjectID INT,
    ProjectName NVARCHAR(50),
    ProjectManagerID INT,
    Department NVARCHAR(50)
);

CREATE TABLE Participations (
    EmployeeID INT,
    ProjectID INT
);

-- Insert Employees
INSERT INTO Employees (EmployeeID, Name, Department, Role) VALUES
(1, 'Alice', 'HR', 'Manager'),
(2, 'Bob', 'Engineering', 'Developer'),
(3, 'Charlie', 'Engineering', 'Manager'),
(4, 'David', 'HR', 'Recruiter'),
(5, 'Eve', 'Marketing', 'Designer'),
(6, 'Frank', 'Marketing', 'Manager');

-- Insert Projects
INSERT INTO Projects (ProjectID, ProjectName, ProjectManagerID, Department) VALUES
(101, 'HR Transformation', 1, 'HR'),
(102, 'Product Development', 2, 'Engineering'),
(103, 'Market Research', 6, 'Marketing'),
(104, 'Employee Onboarding', 1, 'HR');

-- Insert Participations
INSERT INTO Participations (EmployeeID, ProjectID) VALUES
(1, 101), (2, 102), (3, 102), (4, 104), (5, 103), (6, 103), (2, 104);

CREATE TABLE Contractors (
    ContractorID INT,
    Name NVARCHAR(50),
    Department NVARCHAR(50)
);

INSERT INTO Contractors (ContractorID, Name, Department) VALUES
(3, 'Charlie', 'Engineering'),
(4, 'David', 'Marketing'),
(5, 'Eve', 'Engineering'),
(6, 'Frank', 'HR');

[ragavkumarv]

Multi Joins

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(50),
    DepartmentID INT
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(50),
    LocationID INT
);

CREATE TABLE Locations (
    LocationID INT PRIMARY KEY,
    LocationName NVARCHAR(50)
);

CREATE TABLE Projects (
    ProjectID INT PRIMARY KEY,
    ProjectName NVARCHAR(50),
    EmployeeID INT
);

CREATE TABLE Salaries (
    EmployeeID INT PRIMARY KEY,
    AnnualSalary DECIMAL(10, 2)
);

INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID) VALUES
(1, 'Alice', 101),
(2, 'Bob', 102),
(3, 'Charlie', 103);

INSERT INTO Departments (DepartmentID, DepartmentName, LocationID) VALUES
(101, 'HR', 201),
(102, 'Engineering', 202),
(103, 'Marketing', 203);

INSERT INTO Locations (LocationID, LocationName) VALUES
(201, 'New York'),
(202, 'San Francisco'),
(203, 'Chicago');

INSERT INTO Projects (ProjectID, ProjectName, EmployeeID) VALUES
(501, 'Project Alpha', 1),
(502, 'Project Beta', 2),
(503, 'Project Gamma', 3);

INSERT INTO Salaries (EmployeeID, AnnualSalary) VALUES
(1, 60000),
(2, 80000),
(3, 75000);

Exercise 1: Get employee names, their department, location, and salary

Exercise 2: Get all employees, their departments, their project details, and location

Exercise 3: Get the total salary expenditure per department

Exercise 4: List employees and their projects, including those without projects, along with their department and location

Exercise 5: Get the list of employees working in 'San Francisco' and their projects

[ragavkumarv]

Assingment (19 June 2024)

Task: Normalize the below table and Draw the ERD diagram for the same

[ragavkumarv]

Denormalized Data for Movie Schema

MovieID	MovieName	ReleaseYear	DirectorName	ActorName
1	Baahubali	2015	S. S. Rajamouli	Prabhas
2	Ala Vaikunthapurramuloo	2020	Trivikram Srinivas	Allu Arjun
3	Pokiri	2006	Puri Jagannadh	Mahesh Babu
4	Magadheera	2009	S. S. Rajamouli	Ram Charan
5	Srimanthudu	2015	Koratala Siva	Mahesh Babu
6	Arjun Reddy	2017	Sandeep Reddy Vanga	Vijay Deverakonda
7	Geetha Govindam	2018	Parasuram	Vijay Deverakonda
8	Temper	2015	Puri Jagannadh	Jr. NTR
9	Eega	2012	S. S. Rajamouli	Nani
10	Attarintiki Daredi	2013	Trivikram Srinivas	Pawan Kalyan

[ragavkumarv]

CREATE TABLE Movies (
    MovieID INT PRIMARY KEY,
    Title NVARCHAR(100),
    ReleaseYear INT,
    Director NVARCHAR(100),
    Genre NVARCHAR(50),
    Budget DECIMAL(18, 2),
    BoxOffice DECIMAL(18, 2)
);

CREATE TABLE Actors (
    ActorID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    BirthDate DATE
);

CREATE TABLE MovieActors (
    MovieID INT,
    ActorID INT,
    Role NVARCHAR(100),
    PRIMARY KEY (MovieID, ActorID),
    FOREIGN KEY (MovieID) REFERENCES Movies(MovieID),
    FOREIGN KEY (ActorID) REFERENCES Actors(ActorID)
);
-- Movies
INSERT INTO Movies (MovieID, Title, ReleaseYear, Director, Genre, Budget, BoxOffice) VALUES
(1, 'Baahubali: The Beginning', 2015, 'S. S. Rajamouli', 'Action', 1800000000, 6500000000),
(2, 'Baahubali: The Conclusion', 2017, 'S. S. Rajamouli', 'Action', 2500000000, 18000000000),
(3, 'Sye', 2004, 'S. S. Rajamouli', 'Sports Drama', 120000000, 250000000),
(4, 'Magadheera', 2009, 'S. S. Rajamouli', 'Fantasy', 400000000, 1500000000),
(5, 'Arjun Reddy', 2017, 'Sandeep Reddy Vanga', 'Romance', 50000000, 510000000),
(6, 'Rangasthalam', 2018, 'Sukumar', 'Drama', 60000000, 2160000000),
(7, 'Maharshi', 2019, 'Vamsi Paidipally', 'Drama', 100000000, 1750000000),
(8, 'Geetha Govindam', 2018, 'Parasuram', 'Romantic Comedy', 15000000, 1300000000),
(9, 'Ala Vaikunthapurramuloo', 2020, 'Trivikram Srinivas', 'Action Comedy', 100000000, 2620000000),
(10, 'Sarileru Neekevvaru', 2020, 'Anil Ravipudi', 'Action', 75000000, 2600000000);

-- Actors
INSERT INTO Actors (ActorID, FirstName, LastName, BirthDate) VALUES
(1, 'Prabhas', 'Raju', '1979-10-23'),
(2, 'Rana', 'Daggubati', '1984-12-14'),
(3, 'Ram', 'Charan', '1985-03-27'),
(4, 'Vijay', 'Deverakonda', '1989-05-09'),
(5, 'Mahesh', 'Babu', '1975-08-09'),
(6, 'Allu', 'Arjun', '1983-04-08'),
(7, 'Samantha', 'Akkineni', '1987-04-28'),
(8, 'Pooja', 'Hegde', '1990-10-13'),
(9, 'Rashmika', 'Mandanna', '1996-04-05'),
(10, 'Anushka', 'Shetty', '1981-11-07');

-- MovieActors
INSERT INTO MovieActors (MovieID, ActorID, Role) VALUES
(1, 1, 'Baahubali'),
(1, 2, 'Bhallaladeva'),
(2, 1, 'Baahubali'),
(2, 2, 'Bhallaladeva'),
(4, 3, 'Kala Bhairava'),
(5, 4, 'Arjun Reddy'),
(7, 5, 'Rishi'),
(9, 6, 'Bantu'),
(10, 5, 'Ajay Krishna'),
(9, 8, 'Ammu');

Exercise 1: View for Movies Released After 2015 Task: Create a view named ViewMoviesAfter2015 that selects movies released after the year 2015.

Exercise 2: View for High Box Office Movies Task: Create a view named ViewHighBoxOfficeMovies that selects movies with a box office collection greater than 1 billion.

Exercise 3: View for Actor Details in Movies Task: Create a view named ViewActorDetailsInMovies that joins Movies and Actors through MovieActors and shows movie titles and actor names.

More fun ones 🎊🎊😁

Exercise 4: View for Top Grossing Movies per Genre Task: Create a view named ViewTopGrossingMoviesPerGenre that shows the highest-grossing movie in each genre.

Exercise 5: View for Actor's Total Box Office Collection Task: Create a view named ViewActorTotalBoxOffice that shows the total box office collection for each actor across all their movies.

Exercise 6: View for Actor's Age and Movie Roles Task: Create a view named ViewActorAgeAndRoles that shows each actor's age when acted that movie & also their current age and the roles they played in different movies.

Functions Ex

Exercise 1: Scalar Function to Calculate Movie Age Task: Create a scalar function named dbo.CalculateMovieAge that takes a MovieID and returns the age of the movie in years.

Exercise 2: Inline Table-Valued Function for Movies within Budget Range Task: Create an inline table-valued function named dbo.GetMoviesByBudgetRange that takes MinBudget and MaxBudget and returns movies within that budget range.

Exercise 3: Inline Table-Valued Function for Actor's Movies by Year Task: Create an inline table-valued function named dbo.GetActorMoviesByYear that takes an ActorID and Year and returns the movies the actor appeared in that year.

Exercise 4: Multi-Statement Table-Valued Function for Top Actors by Movie Count Task: Create a multi-statement table-valued function named dbo.GetTopActorsByMovieCount that returns actors who have acted in more than 2 movies.

Exercise 5: Multi-Statement Table-Valued Function for Actors with Multiple Roles Task: Create a multi-statement table-valued function named dbo.GetActorsWithMultipleRoles that returns actors who have played more than one role in the same movie.

[ragavkumarv]

Control flow

Task 1: Categorize Movies Based on Box Office Collections Task: Create a query to categorize movies into three groups based on their box office collections: 'Blockbuster', 'Hit', and 'Average'. Use the following criteria:

• Blockbuster: BoxOffice > 10,000,000,000
• Hit: BoxOffice between 1,000,000,000 and 10,000,000,000
• Average: BoxOffice < 1,000,000,000

Task 2: Determine Actor's Age Group Task: Create a query to determine the age group of each actor based on their birth date. The age groups are 'Young' (age < 30), 'Middle-aged' (age between 30 and 50), and 'Senior' (age > 50).

Task 3: Evaluate Movie Profitability Task: Create a query to evaluate the profitability of each movie. Consider a movie 'Profitable' if BoxOffice > Budget and 'Not Profitable' if BoxOffice <= Budget.

[ragavkumarv]

SELECT
    region,
    product_type,
    sales_amount
from sales_data

SELECT
    region,
    product_type,
    sales_amount,
    Sum(sales_amount) OVER (ORDER by sales_amount)
from sales_data

SELECT
    region,
    product_type,
    sales_amount,
    Sum(sales_amount) OVER (ORDER by sales_amount) as Running_Total,
    Avg(sales_amount) OVER (ORDER by sales_amount)  as Running_Avg,
    Count(sales_amount) OVER (ORDER by sales_amount) as Running_Count
from sales_data

Select *
from sales_data

-- Starting of the table to current row
-- Problem: Same values in sales_amount
SELECT
    region,
    product_type,
    sales_amount,
    Sum(sales_amount) OVER (ORDER by sales_amount) as Running_Total,
    Avg(sales_amount) OVER (ORDER by sales_amount)  as Running_Avg,
    Count(sales_amount) OVER (ORDER by sales_amount) as Running_Count
from sales_data;

SELECT
    region,
    product_type,
    sales_amount,
    Sum(sales_amount) OVER (ORDER by sales_amount) as Old_Running_Total,
    Sum(sales_amount) OVER (ORDER by sales_amount ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as New_Running_Total,
    Avg(sales_amount) OVER (ORDER by sales_amount ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  as Running_Avg,
    Count(sales_amount) OVER (ORDER by sales_amount ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Running_Count
from sales_data;

SELECT
    region,
    product_type,
    sales_amount,
    Sum(sales_amount) OVER (ORDER by sales_amount) as Old_Running_Total,
    -- 1 row before and 1 row after
    Sum(sales_amount) OVER (ORDER by sales_amount ROWS BETWEEN 1 PRECEDING AND 1 following) as New_Running_Total,
    Avg(sales_amount) OVER (ORDER by sales_amount ROWS BETWEEN 1 PRECEDING AND 1 following)  as Running_Avg,
    Count(sales_amount) OVER (ORDER by sales_amount ROWS BETWEEN 1 PRECEDING AND 1 following) as Running_Count
from sales_data;

-- Unbounded PRECEDING -- starting of partition | Unbounded following -- ending of partition
SELECT
    region,
    product_type,
    sales_amount,
    Sum(sales_amount) OVER (PARTITION By region  ORDER by sales_amount ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Running_Total,
    Avg(sales_amount) OVER (PARTITION By region  ORDER by sales_amount ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  as Running_Avg,
    Count(sales_amount) OVER (PARTITION By region  ORDER by sales_amount ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Running_Count
from sales_data;

SELECT
    region,
    product_type,
    sales_amount,
    Sum(sales_amount) OVER (  ORDER by sales_amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED following) as Running_Total,
    Avg(sales_amount) OVER (  ORDER by sales_amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED following)  as Running_Avg,
    Count(sales_amount) OVER (  ORDER by sales_amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED following) as Running_Count
from sales_data;

SELECT
    region,
    product_type,
    sales_amount,
    Sum(sales_amount) OVER (PARTITION by region  ORDER by sales_amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED following) as Running_Total,
    Avg(sales_amount) OVER (PARTITION by region   ORDER by sales_amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED following)  as Running_Avg,
    Count(sales_amount) OVER (PARTITION by region   ORDER by sales_amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED following) as Running_Count
from sales_data;

[ragavkumarv]

Sets Questions 2h 30mins - 76 marks

Set 1

https://gist.github.com/ragavkumarv/3df3a48d7bac312588592a41b12e764b

Set 2

https://gist.github.com/ragavkumarv/f60fb3b867dfc6f42b68081872f2cd3b

Set 3

https://gist.github.com/ragavkumarv/237cdcf9927052a83bd3e67f1762ac13

Submission

https://forms.gle/L95zyK5jAXvPihXz5

Correction

https://forms.gle/Ms7uNFzizzbZbWsN8

[ragavkumarv]

Q1. Implement getUserAge function

const userData = {
  user: {
    profile: {
      name: "Alice",
      age: null,
    },
  },
};

console.log(getUserAge(userData)); // Should print: Age not provided
console.log(getUserAge({ user: { profile: { age: 25 } } })); // Should print: 25
console.log(getUserAge({ user: {} })); // Should print: Age not provided

Q2. Write a function that returns a new array with an additional skill for each employee

Clue

Destructuring and the spread operator

const employees = [
  { id: 1, name: "Alice", position: "Developer", skills: ["JavaScript", "React"] },
  { id: 2, name: "Bob", position: "Manager", skills: ["Leadership", "Communication"] },
  { id: 3, name: "Charlie", position: "CEO", skills: ["Strategy", "Vision"] },
];

console.log(addSkill(employees, "Problem Solving"));
// Should print: 
// [
//   { id: 1, name: "Alice", position: "Developer", skills: ["JavaScript", "React", "Problem Solving"] },
//   { id: 2, name: "Bob", position: "Manager", skills: ["Leadership", "Communication", "Problem Solving"] },
//   { id: 3, name: "Charlie", position: "CEO", skills: ["Strategy", "Vision", "Problem Solving"] }
// ]

Q3. Find the total price of all products using arrow functions and array methods - Implement getTotalPrice

const products = [
  { id: 1, name: "Laptop", price: 1000 },
  { id: 2, name: "Phone", price: 500 },
  { id: 3, name: "Tablet", price: 700 },
];

console.log(getTotalPrice(products)); // Should print: 2200

Q4: Write a function that extracts the user's name and theme, providing default values if they are missing

const userProfile = {
  id: 1,
  name: "Eve",
  settings: {
    theme: "dark",
  },
};

console.log(getUserSettings(userProfile)); // Should print: Eve prefers the dark theme
console.log(getUserSettings({ id: 2, name: "Adam" })); // Should print: Adam prefers the light theme

Q5: Write a function that takes a user object and returns a message indicating if the user is active or not

const user1 = { name: "Jane", active: true };
const user2 = { name: "John", active: false };

console.log(getUserStatus(user1)); // Should print: Jane is currently active
console.log(getUserStatus(user2)); // Should print: John is currently inactive

Q6: Write a function that returns the user's age if available, or a default message if not, using nullish coalescing and optional chaining

const userWithFullProfile = {
  id: 1,
  name: "Alice",
  profile: {
    age: 25,
    address: {
      city: "Wonderland",
      zip: "12345",
    },
  },
};

const userWithPartialProfile = {
  id: 2,
  name: "Bob",
  profile: {
    age: null,
  },
};

console.log(getUserAge(userWithFullProfile)); // Should print: 25
console.log(getUserAge(userWithPartialProfile)); // Should print: Age not provided
console.log(getUserAge({ id: 3, name: "Charlie" })); // Should print: Age not provided

Q7: Write a function that generates a summary string for the top scorer using template literals and array methods

const scores = [
  { name: "Alice", score: 90 },
  { name: "Bob", score: 85 },
  { name: "Charlie", score: 92 },
];

console.log(getTopScorer(scores)); // Should print: Charlie is the top scorer with a score of 92.

Q8: Create a function that returns a greeting message based on the time of day

Clue

Use Date methods

// Ex26 - Create a function that returns a greeting message based on the time of day

console.log(greet("Alice")); // Good morning, Alice! (If the time 10am)
console.log(greet("Alice")); // Good afternoon, Alice! (If the time 1pm)
console.log(greet("Alice")); // Good evening, Alice! (If the time 7pm)

Q9: Write a function that returns the name of a user's first friend using nested destructuring

const users = [
  { id: 1, name: "Alice", friends: [{ name: "Bob" }, { name: "Charlie" }] },
  { id: 2, name: "David", friends: [{ name: "Eve" }, { name: "Frank" }] },
  { id: 4, name: "Deepak", friends: [] },
];

console.log(getFirstFriendName(1)); // Should print: Bob
console.log(getFirstFriendName(2)); // Should print: Eve
console.log(getFirstFriendName(3)); // Should print: User not found
console.log(getFirstFriendName(4)); // Should print: Deepak has no friends 🥲

Setup

const movies = [
  {
    id: 1,
    title: "Baahubali",
    director: "S. S. Rajamouli",
    year: 2015,
    ratings: [8, 9, 10],
    genre: "Action",
  },
  {
    id: 2,
    title: "Arjun Reddy",
    director: "Sandeep Reddy Vanga",
    year: 2017,
    ratings: [9, 8, 9],
    genre: "Drama",
  },
  {
    id: 3,
    title: "Mahanati",
    director: "Nag Ashwin",
    year: 2018,
    ratings: [10, 9, 8],
    genre: "Biography",
  },
  {
    id: 4,
    title: "Eega",
    director: "S. S. Rajamouli",
    year: 2012,
    ratings: [7, 8, 9],
    genre: "Fantasy",
  },
  {
    id: 5,
    title: "Jersey",
    director: "Gowtam Tinnanuri",
    year: 2019,
    ratings: [9, 9, 8],
    genre: "Sports",
  },
];

Q10. Write a function that finds a movie by id and returns its title and genre in a formatted string

console.log(getMovieDetails(1)); // Should print: Baahubali is an Action movie
console.log(getMovieDetails(5)); // Should print: Jersey is a Sports movie
console.log(getMovieDetails(6)); // Should print: Movie not found

Q11. Write a function that filters out movies released before a certain year and calculates the average rating of the remaining movies (Rating must be rounded)

console.log(getAverageRatingAfterYear(2016)); // Should print: 8.83 (average rating of Arjun Reddy, Mahanati, and Jersey)
console.log(getAverageRatingAfterYear(2020)); // Should print: No movies after the specified year

Q12: Write a function that checks if all movies of a certain genre have ratings above a certain value

console.log(allRatingsAboveForGenre(7, "Action")); // Should print: Yes, all Action movies are above 7 ratings
console.log(allRatingsAboveForGenre(8, "Biography")); // Should print: No, not all Biography movies are above 8 ratings

Q13: Write a function that returns a string with each movie's title and its ratings joined by commas

console.log(getTitlesAndRatings());
// Should print: Baahubali: 8, 9, 10 | Arjun Reddy: 9, 8, 9 | Mahanati: 10, 9, 8 | Eega: 7, 8, 9 | Jersey: 9, 9, 8

Q14: Write a function that returns a single array containing all ratings of all movies

console.log(getAllRatings());
// Should print: [8, 9, 10, 9, 8, 9, 10, 9, 8, 7, 8, 9, 9, 9, 8]

Q15: Write a function that returns an array of titles of movies that have ratings above a certain threshold in any rating

console.log(getTitlesWithHighRatings(9)); // Should print: ["Baahubali", "Arjun Reddy", "Mahanati", "Jersey"]
console.log(getTitlesWithHighRatings(10)); // Should print: ["Mahanati"]

Q16: Write a function that returns an array of movie titles sorted by their average ratings in descending order

console.log(getTitlesSortedByAverageRating()); // Should print: ["Baahubali", "Arjun Reddy", "Jersey", "Mahanati", "Eega"]

Q17: Write a function that finds the movie with the highest average rating and returns its title

console.log(getMovieWithHighestAverageRating()); // Should print: Baahubali

Q18: Write a function that returns an array of movie titles released after a certain year

console.log(getTitlesAfterYear(2015)); // Should print: ["Arjun Reddy", "Mahanati", "Jersey"]
console.log(getTitlesAfterYear(2018)); // Should print: ["Jersey"]

Q19: Write a function that finds a movie by its title and returns a formatted string with its director and year

console.log(getMovieInfoByTitle("Baahubali")); // Should print: Baahubali directed by S. S. Rajamouli was released in 2015
console.log(getMovieInfoByTitle("Jersey")); // Should print: Jersey directed by Gowtam Tinnanuri was released in 2019
console.log(getMovieInfoByTitle("Avatar")); // Should print: Movie not found

Q20: Write a function that returns an array of titles of movies that have at least one rating below a certain threshold

console.log(getTitlesWithLowRatings(8)); // Should print: ["Eega"]
console.log(getTitlesWithLowRatings(9)); // Should print: ["Baahubali", "Mahanati", "Eega", "Jersey"]

Q21: Write a function that calculates the total number of ratings for movies of a specific genre

console.log(getTotalRatingsByGenre("Action")); // Should print: 3
console.log(getTotalRatingsByGenre("Drama")); // Should print: 3

Q22: Write a function that returns an array of movies where the average rating is above a certain value, including the average rating in the result

console.log(getMoviesWithHighAverageRating(8.5));
// Should print: ["Baahubali has an average rating of 9.00", "Arjun Reddy has an average rating of 8.67", "Jersey has an average rating of 8.67"]

Q23: Write a function that returns an array of movie titles directed by a specific director, sorted by year in ascending order

console.log(getTitlesByDirectorSortedByYear("S. S. Rajamouli")); // Should print: ["Eega", "Baahubali"]
console.log(getTitlesByDirectorSortedByYear("Nag Ashwin")); // Should print: ["Mahanati"]

Q24: Write a function that returns the average rating of movies released in a specific year

console.log(getAverageRatingByYear(2018)); // Should print: 9.00
console.log(getAverageRatingByYear(2015)); // Should print: "No movies released in the specified year"

Q25: Write a function that returns an array of objects with movie titles and their highest ratings

console.log(getMoviesWithHighestRatings());
// Should print: [{ title: "Baahubali", highestRating: 10 }, { title: "Arjun Reddy", highestRating: 9 }, { title: "Mahanati", highestRating: 10 }, { title: "Eega", highestRating: 9 }, { title: "Jersey", highestRating: 9 }]

Q26: Write a function that returns the director with the most movies directed

console.log(getDirectorWithMostMovies()); // Should print the director with the most movies

Q27: Write a function that merges two arrays of movies into one using the spread operator

console.log(mergeMovies(movies, moreMovies));
// Should print: array with all 7 movies

Q28: Write a function that accepts any number of movie objects and returns an array of their titles using the rest operator

console.log(getTitles(...movies)); // Should print titles of all movies in the array
console.log(getTitles(movies[0], movies[1])); // Should print: ["Baahubali", "Arjun Reddy"]

Q29: Write a function that merges two arrays of movies into one using the spread operator and provides a default value for the second array

const moreMovies = [
  {
    id: 6,
    title: "RRR",
    director: "S. S. Rajamouli",
    year: 2022,
    ratings: [10, 10, 9],
    genre: "Action",
  },
  {
    id: 7,
    title: "Pushpa",
    director: "Sukumar",
    year: 2021,
    ratings: [8, 9, 8],
    genre: "Action",
  },
];

console.log(mergeMovies(movies, moreMovies)); // Should print the merged array of movies
console.log(mergeMovies(movies)); // Should print the original array of movies

Q30: Write a function that returns the last N movie titles, using slice and spread operator with a default value for N

console.log(getLastNMovieTitles()); // Should print the last 3 movie titles
console.log(getLastNMovieTitles(2)); // Should print the last 2 movie titles

Q31: Write a function that accepts multiple movie IDs, fetches the titles, and returns a formatted string using the rest operator, nullish coalescing, and template literals Interesting

console.log(getMovieTitlesByIds(1, 3, 5));
// Should print: Selected Movies: Baahubali, Mahanati, Jersey
console.log(getMovieTitlesByIds(1, 6));
// Should print: Selected Movies: Baahubali, Unknown Title
console.log(getMovieTitlesByIds(5, 1));
// Should print: Selected Movies: Jersey, Baahubali,

Q32: Write a function that accepts any number of movies and returns a formatted string listing their titles and genres using the rest operator, nullish coalescing, and template literals Interesting

console.log(listMovies(...movies));
// Should print: Baahubali (Action), Arjun Reddy (Drama), Mahanati (Biography), Eega (Fantasy), Jersey (Sports)
console.log(listMovies(movies[0], movies[1], movie[111]));
// Should print: Baahubali (Action), Arjun Reddy (Drama), Unknown Title (Unknown Genre)

Q33: Write a function that calculates the total number of ratings for each director Challenging

// Ex81 -
const getTotalRatingsForDirectors = () => {
  return movies.reduce((acc, movie) => {
    acc[movie.director] = (acc[movie.director] || 0) + movie.ratings.length;
    return acc;
  }, {});
};

console.log(getTotalRatingsForDirectors());
// Should print: { "S. S. Rajamouli": 6, "Sandeep Reddy Vanga": 3, "Nag Ashwin": 3, "Gowtam Tinnanuri": 3 }

Q34: Write a function that returns an array of genres sorted by the total number of ratings received by movies in that genre Challenging

console.log(getGenresSortedByTotalRatings()); // Should print genres sorted by total ratings

Q35: Write a function that returns an array of movie titles directed by directors who have directed more than one movie Challenging

console.log(getTitlesByDirectorsWithMultipleMovies()); // Should print: ["Baahubali", "Eega"]

Q36: Write a function that calculates the total number of ratings for each genre and returns the genre with the highest total ratings Challenging

console.log(getGenreWithHighestTotalRatings()); // Should print the genre with the highest total ratings

Q37: Write a function that returns an array of directors who have directed movies with an average rating above a certain value Challenging

console.log(getDirectorsWithHighAverageRatings(8.5)); // Should print directors with high average ratings

Q38: Write a function that updates a movie's genre and ratings by ID, using object destructuring, spread operator, and default values Challenging

console.log(updateMovieDetails(2, { genre: "Romance", ratings: [10, 9, 8] }));
// Should print updated Arjun Reddy
console.log(updateMovieDetails(6, { genre: "Thriller" }));
// Should print: Movie not found

Q39: Update or add a movie based on the id Challenging

console.log(
  updateOrAddMovie({
    id: 6,
    title: "Pushpa",
    director: "Sukumar",
    year: 2021,
    ratings: [8, 9, 8],
    genre: "Action",
  })
);
// Should add Pushpa to the list
console.log(
  updateOrAddMovie({
    id: 5,
    title: "Jersey",
    director: "Gowtam Tinnanuri",
    year: 2019,
    ratings: [10, 10, 9],
    genre: "Sports",
  })
);
// Should update Jersey's ratings in the list

[Rajendra1296]

[Rajendra1296]

Q4 can be done using destructuring & nullish coalescing

[ragavkumarv]

Task - Add as dropdown in the Add / Edit Recipe form

const CUISINES = [
  { label: 'Italian', value: 'italian' },
  { label: 'Chinese', value: 'chinese' },
  { label: 'Mexican', value: 'mexican' },
  { label: 'Indian', value: 'indian' },
  { label: 'Japanese', value: 'japanese' },
  { label: 'French', value: 'french' },
  { label: 'Thai', value: 'thai' },
  { label: 'Greek', value: 'greek' },
  { label: 'Spanish', value: 'spanish' },
  { label: 'Middle Eastern', value: 'middle-eastern' },
  { label: 'Korean', value: 'korean' },
  { label: 'American', value: 'american' },
  { label: 'Vietnamese', value: 'vietnamese' },
  { label: 'Caribbean', value: 'caribbean' },
  { label: 'Ethiopian', value: 'ethiopian' }
];

Forms - Proclink

https://codepen.io/ragavkumarv/pen/YzoZEKq?editors=1000

[ragavkumarv]

Name	Set
Guna Sekhar	1
Mohammad Sumayya Afrin	2
Sanala Tejaswini	1
Akshaya Palle	2
Aishwarya Pola	1
Nikhil Raj	2
Nithin Kumar Madduri	1
Rishika	2
Srujan Pothu	1
Sai Phanihdhar	2
Bhanusri	1
NavyaSree	2

Weekend Assignment

https://gist.github.com/ragavkumarv/f3280753c9f2a00560a7bccc538be701

[ragavkumarv]

Angular test

[ragavkumarv]

mean-week-4-test

https://gist.github.com/ragavkumarv/608ddb2681caf71c9ffeabf684eae103