Joining Assignment , Insert into select , database backup

create table salesman(salesman_id int , name varchar(500), city varchar(500) , commission int );

create table customer (customer_id int , cust_name varchar(500) , city varchar(500), grade int , salesman_id int );

create table orders(ord_no int , purch_amt int , customer_id int , salesman_id int)

create table orders(ord_no int , purch_amt int , customer_id int , salesman_id int , ord_date varchar(20))

insert into salesman values(101,'Ramesh', 'Nagpur','12.23')

insert into salesman values(102,'suresh', 'pune','15.23')

insert into salesman values(103,'mahesh', 'delhi','16.23')

insert into salesman values(104,'raj', 'pune','16.23')

insert into salesman values(105,'rahul', 'mumbai','17.23')

insert into customer values(201,'Mr. Raj', 'Nagpur','100',101)

insert into customer values(202,'Mr. Rahul', 'Pune','100',102)

insert into customer values(203,'Mr. Rakesh', 'Delhi','100',103)

insert into customer values(204,'Mr. Akash', 'Jaipur','100',104)

insert into orders values(601, 1250, 201,101)

insert into orders values(602, 1251, 202,102)

insert into orders values(603, 1252, 203,103)

insert into orders values(604, 1253, 204,104)

Q1 .

From the following tables, write a SQL query to find the salespersons and customers who live in same city. Return customer name, salesperson name and salesperson city.

SELECT customer.cust_name,

salesman.name, salesman.city

FROM salesman, customer

WHERE salesman.city = customer.city;

From the following tables, write a SQL query to find all the customers along with the salesperson who works for them. Return customer name, and salesperson name.

SELECT customer.cust_name, salesman.name

FROM customer,salesman

WHERE salesman.salesman_id = customer.salesman_id;

From the following tables, write a SQL query to find those salespeople who generated orders for their customers but not located in the same city. Return ord_no, cust_name, customer_id (orders table), salesman_id (orders table).

SELECT ord_no, cust_name, orders.customer_id, orders.salesman_id

FROM salesman, customer, orders

WHERE customer.city <> salesman.city

AND orders.customer_id = customer.customer_id

AND orders.salesman_id = salesman.salesman_id;

From the following tables, write a SQL query to find those orders made by customers. Return order number, customer name.

SELECT orders.ord_no, customer.cust_name

FROM orders, customer

WHERE orders.customer_id = customer.customer_id;

From the following tables, write a SQL query to find those customers where each customer has a grade and served by at least a salesperson who belongs to a city. Return cust_name as "Customer", grade as "Grade".

SELECT customer.cust_name AS "Customer",

customer.grade AS "Grade"

FROM orders, salesman, customer

WHERE orders.customer_id = customer.customer_id

AND orders.salesman_id = salesman.salesman_id

AND salesman.city IS NOT NULL

AND customer.grade IS NOT NULL;

From the following table, write a SQL query to find those customers who served by a salesperson and the salesperson works at the commission in the range 12% to 14% (Begin and end values are included.). Return cust_name AS "Customer", city AS "City".

SELECT customer.cust_name AS "Customer",

customer.city AS "City",

salesman.name AS "Salesman",

salesman.commission

FROM customer,salesman

WHERE customer.salesman_id = salesman.salesman_id

AND salesman.commission

BETWEEN .12 AND .14;

From the following tables write a SQL query to find those salespersons who received a commission from the company more than 12%. Return Customer Name, customer city, Salesman, commission.

SELECT a.cust_name AS "Customer Name",

a.city, b.name AS "Salesman", b.commission

FROM customer a

INNER JOIN salesman b

ON a.salesman_id=b.salesman_id

WHERE b.commission>.12;

From the following tables write a SQL query to find those salespersons do not live in the same city where their customers live and received a commission from the company more than 12%. Return Customer Name, customer city, Salesman, salesman city, commission.

SELECT a.cust_name AS "Customer Name",

a.city, b.name AS "Salesman", b.city,b.commission

FROM customer a

INNER JOIN salesman b

ON a.salesman_id=b.salesman_id

WHERE b.commission>.12

AND a.city<>b.city;

From the following tables write a SQL query to display the cust_name, customer city, grade, Salesman, salesman city. The result should be ordered by ascending on customer_id.

SELECT a.cust_name,a.city,a.grade,

b.name AS "Salesman",b.city

FROM customer a

LEFT JOIN salesman b

ON a.salesman_id=b.salesman_id

order by a.customer_id;

From the following tables write a SQL query to find those customers whose grade less than 300. Return cust_name, customer city, grade, Salesman, saleman city. The result should be ordered by ascending customer_id.

SELECT a.cust_name,a.city,a.grade,

b.name AS "Salesman", b.city

FROM customer a

LEFT OUTER JOIN salesman b

ON a.salesman_id=b.salesman_id

WHERE a.grade<300

ORDER BY a.customer_id;

Write a SQL statement to make a report with customer name, city, order number, order date, and order amount in ascending order according to the o Name to find that either any of the existing customers have placed no order or placed one or more orders.

SELECT a.cust_name,a.city, b.ord_no,

b.purch_amt AS "Order Amount"

FROM customer a

LEFT OUTER JOIN orders b

ON a.customer_id=b.customer_id

order by a.cust_name;

Write a SQL statement to make a report with customer name, city, order number, order date, order amount salesman name and commission to find that either any of the existing customers have placed no order or placed one or more orders by their salesman or by own.

create table orders(ord_no int , purch_amt int , customer_id int , salesman_id int , ord_date varchar(20))

SELECT a.cust_name,a.city, b.ord_no,

b.ord_date,b.purch_amt AS "Order Amount",

c.name,c.commission

FROM customer a

LEFT OUTER JOIN orders b

ON a.customer_id=b.customer_id

LEFT OUTER JOIN salesman c

ON c.salesman_id=b.salesman_id;

Write a SQL statement to make a list in ascending order for the salesmen who works either for one or more customer or not yet join under any of the customers.

SELECT a.cust_name,a.city,a.grade,

b.name AS "Salesman", b.city

FROM customer a

RIGHT OUTER JOIN salesman b

ON b.salesman_id=a.salesman_id

ORDER BY b.salesman_id;

From the following tables write a SQL query to list all salespersons along with customer name, city, grade, order number, date, and amount. Condition for selecting list of salesmen : 1. Salesmen who works for one or more customer or, 2. Salesmen who not yet join under any customer, Condition for selecting list of customer : 3. placed one or more orders, or 4. no order placed to their salesman.

SELECT a.cust_name,a.city,a.grade,

b.name AS "Salesman",

c.ord_no, c.ord_date, c.purch_amt

FROM customer a

RIGHT OUTER JOIN salesman b

ON b.salesman_id=a.salesman_id

RIGHT OUTER JOIN orders c

ON c.customer_id=a.customer_id;

Write a SQL statement to make a list for the salesmen who either work for one or more customers or yet to join any of the customer. The customer, may have placed, either one or more orders on or above order amount 2000 and must have a grade, or he may not have placed any order to the associated supplier.

SELECT a.cust_name,a.city,a.grade,

b.name AS "Salesman",

c.ord_no, c.ord_date, c.purch_amt

FROM customer a

RIGHT OUTER JOIN salesman b

ON b.salesman_id=a.salesman_id

LEFT OUTER JOIN orders c

ON c.customer_id=a.customer_id

WHERE c.purch_amt>=200

AND a.grade IS NOT NULL;

Q10

Write a SQL statement to make a report with customer name, city, order no., order date, purchase amount for those customers from the existing list who placed one or more orders or which order(s) have been placed by the customer who is not on the list.

SELECT a.cust_name,a.city, b.ord_no,

b.ord_date,b.purch_amt AS "Order Amount"

FROM customer a

FULL OUTER JOIN orders b

ON a.customer_id=b.customer_id;

01/01/22

What is a cross join?

A cross join is a type of join that returns the Cartesian product of rows from the tables in the join. In other words, it combines each row from the first table with each row from the second table. This article demonstrates, with a practical example, how to do a cross join in Power Query.1

Q10

Write a SQL query to combine each row of salesman table with each row of customer table. Cross joining

SELECT *

FROM salesman a

CROSS JOIN customer b;

Q11

Write a SQL statement to make a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for that salesman who belongs to a city. Cross joining

SELECT *

FROM salesman a

CROSS JOIN customer b

WHERE a.city IS NOT NULL;

Q12

Write a SQL statement to make a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for those salesmen who belongs to a city and the customers who must have a grade. Cross joining

SELECT *

FROM salesman a

CROSS JOIN customer b

WHERE a.city IS NOT NULL

AND b.grade IS NOT NULL;

Q13

Write a SQL statement to make a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for those salesmen who must belong a city which is not the same as his customer and the customers should have an own grade.

SELECT *

FROM salesman a

CROSS JOIN customer b

WHERE a.city IS NOT NULL

AND b.grade IS NOT NULL

AND a.city<>b.city;

=============================00000=================

Q12

Cross join

SELECT *

FROM salesman a

CROSS JOIN customer b

WHERE a.city IS NOT NULL

AND b.grade IS NOT NULL;

Q13

SELECT *

FROM salesman a

CROSS JOIN customer b

WHERE a.city IS NOT NULL

AND b.grade IS NOT NULL

AND a.city<>b.city;

Tak -2

·         company_mast  (  COM_ID COM_NAME  )

·         item_mast (PRO_ID, PRO_NAME ,PRO_PRICE ,PRO_COM )

create table company_mast(com_id int , com_name varchar(500));

create table item_mast (PRO_ID int , PRO_NAME varchar(200) ,

PRO_PRICE int , PRO_COM int )

Q1

From the following tables write a SQL query to select all rows from both participating tables as long as there is a match between pro_com and com_id.

SELECT *

FROM item_mast

INNER JOIN company_mast

ON item_mast.pro_com= company_mast.com_id;

Write a SQL query to display the item name, price, and company name of all the products.

SELECT item_mast.pro_name, pro_price, company_mast.com_name

FROM item_mast

INNER JOIN company_mast

ON item_mast.pro_com = company_mast.com_id;

From the following tables write a SQL query to calculate the average price of items of each company. Return average value and company name.

SELECT AVG(pro_price), company_mast.com_name

FROM item_mast INNER

JOIN company_mast

ON item_mast.pro_com= company_mast.com_id

GROUP BY company_mast.com_name;

From the following tables write a SQL query to calculate and find the average price of items of each company higher than or equal to Rs. 350. Return average value and company name.

SELECT AVG(pro_price), company_mast.com_name

FROM item_mast INNER JOIN company_mast

ON item_mast.pro_com= company_mast.com_id

GROUP BY company_mast.com_name

HAVING AVG(pro_price) >= 350;

From the following tables write a SQL query to find the most expensive product of each company. Return pro_name, pro_price and com_name.

SELECT A.pro_name, A.pro_price, F.com_name

FROM item_mast A INNER JOIN company_mast F

ON A.pro_com = F.com_id

AND A.pro_price =

(

SELECT MAX(A.pro_price)

FROM item_mast A

WHERE A.pro_com = F.com_id

);

Subqueries

From the following tables, write a SQL query to find all the orders issued by the salesman 'Paul Adam'. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

SELECT *

FROM orders

WHERE salesman_id =

(SELECT salesman_id

FROM salesman

WHERE name='raj');

From the following tables, write a SQL query to find all the orders, which are generated by those salespeople, who live in the city of London.Return ord_no, purch_amt, ord_date, customer_id, salesman_id.

SELECT *

FROM orders

WHERE salesman_id =(SELECT salesman_id

FROM salesman

WHERE city='nagpur');

From the following tables, write a SQL query to find the orders generated by the salespeople who may work for customers whose id is 3007. Return ord_no, purch_amt, ord_date, customer_id, salesman_id.

SELECT *

FROM orders

WHERE salesman_id =

(SELECT DISTINCT salesman_id

FROM orders

WHERE customer_id =201);

From the following tables, write a SQL query to find the orders generated by the salespeople who may work for customers whose id is 3007. Return ord_no, purch_amt, ord_date, customer_id, salesman_id.

SELECT *

FROM orders

WHERE salesman_id =

(SELECT DISTINCT salesman_id

FROM orders

WHERE customer_id =201);

From the following tables, write a SQL query to find the order values greater than the average order value of 10th October 2012. Return ord_no, purch_amt, ord_date, customer_id, salesman_id.

SELECT *

FROM orders

WHERE purch_amt >

(SELECT AVG(purch_amt)

FROM orders

WHERE ord_date ='11/12/2021');

From the following tables, write a SQL query to find the salespeople who had more than one customer. Return salesman_id and name.

SELECT salesman_id,name

FROM salesman a

WHERE 0 <

(SELECT COUNT(*)

FROM customer

WHERE salesman_id=a.salesman_id);

Left outer join

SELECT a.cust_name,a.city,a.grade,

b.name AS "Salesman",

c.ord_no, c.ord_date, c.purch_amt

FROM customer a

RIGHT OUTER JOIN salesman b

ON b.salesman_id=a.salesman_id

RIGHT OUTER JOIN orders c

ON c.customer_id=a.customer_id;

Q7.

SELECT a.cust_name,a.city,a.grade,

b.name AS "Salesman",

c.ord_no, c.ord_date, c.purch_amt

FROM customer a

RIGHT OUTER JOIN salesman b

ON b.salesman_id=a.salesman_id

LEFT OUTER JOIN orders c

ON c.customer_id=a.customer_id

WHERE c.purch_amt>=1258

AND a.grade IS NOT NULL;

SELECT a.cust_name,a.city, b.ord_no,

b.ord_date,b.purch_amt AS "Order Amount"

FROM customer a

FULL OUTER JOIN orders b

ON a.customer_id=b.customer_id;

Write a SQL query to combine each row of salesman table with each row of customer table.

SELECT *

FROM salesman a

CROSS JOIN customer b;

Insert into

INSERT INTO Customers (CustomerName, City, Country)

SELECT SupplierName, City, Country FROM Suppliers;

INSERT INTO Customers (CustomerName, City, Country)

SELECT SupplierName, City, Country FROM Suppliers

WHERE Country='Germany';

Backup database

BACKUP DATABASE mastersoft

TO DISK = 'e:\testDB.bak';

The SQL BACKUP WITH DIFFERENTIAL Statement

A differential back up only backs up the parts of the database that have changed since the last full database backup.

BACKUP DATABASE mastersoft TO DISK = 'e:\pop.bak' WITH DIFFERENTIAL;

Search This Blog

Nrsolution4u SQL

Joining Assignment , Insert into select , database backup

The SQL BACKUP WITH DIFFERENTIAL Statement

Comments

Post a Comment

Popular posts from this blog

Komal Student Notes

SQL SERVER SETTING