MYSQL.STL
MYSQL
Step1 Create database(डेटाबेस बनाने के लिए)
Syntax:-
Create database <database_name>
Create database da9;
Step2 create table(टेबल बनाने के लिए)
Syntax:-
Create table<table_name>
Create table school(id int not null auto_increment,name varchar(20) not null,age int not null);
Step3 table ko dekhne ke liye(टेबल देखने के लिए)
Syntax:-
Select * from table_name(टेबल को देखने के लिए)
Select * from school;
Step4 column mein value insert karne ke liye
(कॉलम में वैल्यू इनसर्ट करने के लिए)
Insert into tablename(.id..,.name..,age...)value(..1..,..”name”..,.age..);
insert into school(name,age)value(“arun”,22);
ALTER COMMAND IN SQL
ALTER command:-
The ALTER Command allows us to alter the Structure of objects like - add a new column, drop a column, Rename column, Rename table, add constraint
(1)add new column/multiple column
(2)drop column/remove
(3)rename column
(4)rename table
(5)add constraint
(1.)How to add a column:-
syntax:-
alter table<table_name>add<field_name><data_type>;
Alter table school add column city varchar(20);
(2)How to add multiple column:-
syntax:-
alter table<table_name>add column<(field_data datatype)>;
Alter table school add column(pincode int,state varchar(20));
(3)How to delete/drop/remove a column:-
Syntax:-
Alter table<table_name>drop column<column_name>
Alter table school drop column state
(4)How to rename column_name:-
Syntax:-
Alter table<table_name> rename<old column name> to rename column
name
Alter table school rename city to town;
5.column me data bharne ke liye:-
syntax:-UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Update school set town=(“patna”) where id=1;
(5)How to rename table_name:-
Alter table<table_name> rename<old column name> to rename table
name
Alter table school rename customer;
Table ko dekhne ke liye:-
Select * from customer;
(6)column ke data ko datatype change karne ke liye:-
For exam:- age ka data type int hai to int ko varchar karna hai
Syntax:-
Alter table<table_name>modify<column>age int varchar
Alter table customer modify column age int varchar
(7)How to add a constraint by the help alter command:-
Syntax:-
Alter table<table_name>add constraint<constraint_name> <constraint_type> <column name>;
(8) constraint ko hatane ke liye:-
Syntax:-
Alter table<table_name>drop constraint<constraint_name>
Category of query language:-
(1)DQL:-Data query language
(2)DDL:-Data definition language
(3)DML:-Data manipulation language
(4)DCL:-Data control language
(5)TCL:-Transaction control language
Data set:-1
use db9;
CREATE TABLE products (
product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL,
category VARCHAR(255) NOT NULL,
stock_quantity INT NOT NULL);
#dekhane ke liye
select * from products;
INSERT INTO products (name, price, category, stock_quantity)
VALUES
('iPhone 14 Pro Max', 1200, 'Electronics', 100),
('MacBook Pro', 2000, 'Electronics', 50),
('Samsung Galaxy S23 Ultra', 1100, 'Electronics', 75),
('Google Pixel 7 Pro', 900, 'Electronics', 125),
('Sony WH-1000XM5', 400, 'Electronics', 25),
('Nike Air Jordan 1', 150, 'Clothing', 200),
('Adidas Yeezy 350', 120, 'Clothing', 150),
('Champion hoodie', 50, 'Clothing', 100),
('Levi's 501 jeans', 100, 'Clothing', 75),
('H&M t-shirt', 20, 'Clothing', 50),
('PlayStation 5', 500, 'Gaming', 50),
('Xbox Series X', 500, 'Gaming', 40),
('Nintendo Switch', 300, 'Gaming', 60),
('LEGO Star Wars Millennium Falcon', 800, 'Toys', 25),
('Hot Wheels Ultimate Garage', 300, 'Toys', 50),
('Barbie Dreamhouse', 150, 'Toys', 75),
('Nerf Fortnite Elite 2.0 blaster', 50, 'Toys', 100),
('Play-Doh Kitchen Creations Ice Cream Treats Set', 20, 'Toys', 125),
('Crayola Super Tips Washable Markers', 10, 'Arts and Crafts', 150),
('Faber-Castell Creative Studio', 50, 'Arts and Crafts', 100);
#QUESTION;-
1.Select all products that are priced at $500 or more.
2.Select all products that are in the "Electronics" category and have a stock quantity of less than 100.
3.Select all products that are not in the "Clothing" category and have a name that starts with the letter "A".
4.Select all products that have a price greater than $100 and less than $200.
5.Select all products that have a stock quantity of zero.
6.Select all products that are in the "Gaming" category and have a name that contains the word "PlayStation".
7.Select all products that are in the "Toys" category and have a price of $100 or less.
8.Select all products that are in the "Arts and Crafts" category and have a stock quantity of more than 150.
9.Select all products that are not in the "Electronics" category and have a name that ends with the letter "e".
10.Select all products that have a price equal to $100
WHERE COMMAND
#Answer;-
Where command:-The WHERE clause is used to filter records
Q(1).Select all products that are priced at $500 or more.
Select * form<table_name>where<column_name> greaterthan(>price)
Ans:-select * from products where price > 500;
Q(2).Select all products that are in the "Electronics" category and have a stock
ans:-select * from products where category="electronics" and stock_quantity<100;
Q(3).Select all products that are not in the "Clothing" category and have a name that starts with the letter "A".
ans:-select * from products where category !="Clothing" and name like 'A%';
Q(4).Select all products that have a price greater than $100 and less than $200.
ans:-select * from products where price > 100 and price < 200;
Q(5).Select all products that have a stock quantity of zero.
ans:-select * from products where stock_quantity=0;
Q(6).Select all products that are in the "Gaming" category and have a name that contains the word "PlayStation".
ans:-select * from products where category="Gaming" and name like'PlayStation%';
Q(7).Select all products that are in the "Toys" category and have a price of $100 or less.
Q(8).Select all products that are in the "Arts and Crafts" category and have a stock quantity of more than 150.
Q(9).Select all products that are not in the "Electronics" category and have a name that ends with the letter "e".
ans:-select * from products where category like 'a%';
ans:-select * from products where category like '%e';
Q(10).Select all products that have a price equal to $100
#QUESTION;-
Here are 10 questions on the UPDATE command in SQL using the table you provided:
(1).Update the price of the iPhone 14 Pro Max to $1300.
(2).Update the stock quantity of the MacBook Pro to 60.
(3).Update the category of the Samsung Galaxy S23 Ultra to "Mobile Devices".
(4).Set the price of the Google Pixel 7 Pro to NULL.
(5).Update the stock quantity of the Sony WH-1000XM5 to 0.
(6).Increase the price of the Nike Air Jordan 1 by 10%.
(7).Update the stock quantity of the Adidas Yeezy 350 to be equal to the stock quantity of the Champion hoodie.
(8).Set the category of the Levi's 501 jeans to "Apparel".
(9).Update the price of the H&M t-shirt to be equal to the price of the Play-Doh Kitchen Creations Ice Cream Treats Set.
(10).Update the stock quantity of the Crayola Super Tips Washable Markers to be equal to the stock quantity of the Faber-Castell Creative Studio.
UPDATE COMMAND
update command:-The UPDATE command is used to update existing rows in a table.
(1.) it is the part of DML
(2.)is used to modification in table data
(3.)diff in ALTER and UPDATE
#Answer;-
Q(1).Update the price of the iPhone 14 Pro Max to $1300.
Update<table_name> set price=1300 where id=1
Exp:-update products set price=1300 where product_id=1;
Q(2.)Update the stock quantity of the MacBook Pro to 60.
update products set stock_quantity=60 where product_id=2;
Q(3.)Update the category of the Samsung Galaxy S23 Ultra to "Mobile Devices".
update products set category= "moblile_device" where product_id=3;
Q(4.)Set the price of the Google Pixel 7 Pro to NULL.
update products set price=0 where product_id=4;
Q(5.)Update the stock quantity of the Sony WH-1000XM5 to 0.
Q(6)Increase the price of the Nike Air Jordan 1 by 10%.
Update products set price=price+price*0.1 where product_id=6;
Q(7.)Update the stock quantity of the Adidas Yeezy 350 to be equal to
update products set stock_quantity=( select stock_quantity from products where product_id=8) where product_id=7;
Q(8)the stock quantity of the Champion hoodie.
update products set category="Apple"where product_id=9;
Q(9)Set the category of the Levi's 501 jeans to "Apparel".
update products set price=20 where product_id=10;
Q(10)Update the price of the H&M t-shirt to be equal to the price of the Play-Doh Kitchen Creations Ice Cream Treats Set.
update products set stock_quantity=100 where product_id=19;
Q(11)Update the stock quantity of the Crayola Super Tips Washable Markers to be equal to the stock quantity of the Faber-Castell Creative Studio.
Data set:-2
Use da3;
CREATE TABLE students (
student_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
grade INT NOT NULL,
age INT NOT NULL,
);
#dekhane ke liye
Select * from students
INSERT INTO students (first_name, last_name, grade, age)
VALUES ('Alice', 'Smith', 1, 6),
('Bob', 'Jones', 2, 7),
('Carol', 'Williams', 3, 8),
('Dave', 'Miller', 4, 9),
('Eve', 'Brown', 5, 10),
('Frank', 'Davis', 6, 11),
('George', 'Johnson', 7, 12),
('Helen', 'Taylor', 8, 13),
('Isaac', 'Garcia', 9, 14),
('Jane', 'Martinez', 10, 15),
('Kevin', 'Anderson', 11, 16),
('Linda', 'Lopez', 12, 17),
('Michael', 'Rodriguez', 13, 18),
('Nicole', 'Thompson', 14, 19),
('Oliver', 'Walker', 15, 20);
#QUESTION;-
Here are 10 questions to practice the DELETE command in SQL using the student data table you provided:
(1).Delete the student with the student ID of 1.
(2).Delete all students in the 3rd grade.
(3).Delete all students who are under the age of 13.
(4).Delete all students whose last name starts with the letter "A".
(5).Delete all students who have not submitted any assignments yet.
(6).Delete all students who have failed more than one test.
(7).Delete all students who have a GPA of less than 2.0.
(8).Delete all students who have been absent from school more than 10 days.
(9).Delete all students who have not been active in school clubs or activities.
(10).Delete all students who are not planning on attending college.
#Answer;-
(1).Delete the student with the student ID of 1.
DELETE FROM students WHERE student_id = 1;
(2).Delete all students in the 3rd grade.
DELETE FROM students WHERE grade = 3;
(3).Delete all students who are under the age of 13.
DELETE FROM students WHERE age < 13;
(4).Delete all students whose last name starts with the letter "A".
DELETE FROM students WHERE last_name LIKE 'A%';
(5).Delete all students who have not submitted any assignments yet.
-- Assuming there's a column named 'assignments_submitted'
DELETE FROM students WHERE assignments_submitted IS NULL;
(6).Delete all students who have failed more than one test.
-- Assuming there's a column named 'failed_tests'
DELETE FROM students WHERE failed_tests > 1;
(7).Delete all students who have a GPA of less than 2.0.
-- Assuming there's a column named 'gpa'
DELETE FROM students WHERE gpa < 2.0;
(8).Delete all students who have been absent from school more than 10 days.
-- Assuming there's a column named 'absent_days'
DELETE FROM students WHERE absent_days > 10;
(9).Delete all students who have not been active in school clubs or activities.
-- Assuming there's a column named 'active_in_clubs'
DELETE FROM students WHERE active_in_clubs = 0;
(10).Delete all students who are not planning on attending college.
-- Assuming there's a column named 'planning_attend_college'
DELETE FROM students WHERE planning_attend_college = 'No';
GROUP BY COMMAND
Data set:-3
Use da 3;
CREATE TABLE ecom(
order_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(255) NOT NULL,
category VARCHAR(50) NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
ordered_city VARCHAR(50) NOT NULL,
state_name VARCHAR(50) NOT NULL,
payment_mode VARCHAR(20) NOT NULL,
delivery_status ENUM('Pending', 'In Progress', 'Delivered', 'Cancelled') NOT NULL
);
#dekhane ke liye
Select * from orders;
INSERT INTO ecom (product_name, category, quantity, unit_price, ordered_city, state_name, payment_mode, delivery_status)
VALUES
('Laptop', 'Electronics', 1, 1200.00, 'New York', 'NY', 'Credit Card', 'In Progress'),
('Phone', 'Electronics', 1, 800.00, 'Los Angeles', 'CA', 'Cash', 'Pending'),
('TV', 'Electronics', 1, 500.00, 'Chicago', 'IL', 'Debit Card', 'Delivered'),
('Dress', 'Clothing', 1, 100.00, 'Houston', 'TX', 'Credit Card', 'In Progress'),
('Shirt', 'Clothing', 1, 50.00, 'Philadelphia', 'PA', 'Cash', 'Pending'),
('Jeans', 'Clothing', 1, 75.00, 'Phoenix', 'AZ', 'Debit Card', 'Delivered'),
('Book', 'Books', 1, 20.00, 'San Antonio', 'TX', 'Credit Card', 'In Progress'),
('Pen', 'Office Supplies', 10, 1.00, 'San Diego', 'CA', 'Cash', 'Pending'),
('Paper', 'Office Supplies', 25, 2.00, 'Dallas', 'TX', 'Debit Card', 'Delivered'),
('Stapler', 'Office Supplies', 1, 5.00, 'San Jose', 'CA', 'Credit Card', 'In Progress'),
('Groceries', 'Food',1, 50.00, 'Jacksonville', 'FL', 'Cash', 'Pending'),
('Fruit', 'Food',3, 20.00, 'Indianapolis', 'IN', 'Debit Card', 'Delivered'),
('Meat', 'Food',5, 30.00, 'Columbus', 'OH', 'Credit Card', 'In Progress'),
('Toys', 'Toys', 1, 30.00, 'Fort Worth', 'TX', 'Cash', 'Pending'),
('Games', 'Toys', 1, 50.00, 'Charlotte', 'NC', 'Debit Card', 'Delivered'),
('Puzzle', 'Toys', 1, 20.00, 'Austin', 'TX', 'Credit Card', 'In Progress'),
('Furniture', 'Furniture', 1, 1000.00, 'Denver', 'CO', 'Cash', 'Pending'),
('Sofa', 'Furniture', 1, 500.00, 'Milwaukee', 'WI', 'Debit Card', 'Delivered'),
('Table', 'Furniture', 1, 300.00, 'Seattle', 'WA', 'Credit Card', 'In Progress'),
('Kitchenware', 'Kitchenware', 10, 10.00, 'San Francisco', 'CA', 'Cash', 'Pending'),
('Pots', 'Kitchenware', 3, 20.00, 'Oklahoma City', 'OK', 'Debit Card', 'Delivered'),
('Pans', 'Kitchenware', 2, 15.00, 'Nashville', 'TN', 'Credit Card', 'In Progress'),
('Appliances', 'Appliances', 1, 500.00, 'El Paso', 'TX', 'Cash', 'Pending'),
('Refrigerator', 'Appliances', 1, 300.00, 'Louisville', 'KY', 'Debit Card', 'Delivered'),
('Washing Machine', 'Appliances', 1, 200.00, 'Tucson', 'AZ', 'Credit Card', 'In Progress'),
('Medicine', 'Medicine', 10, 5.00, 'Baltimore', 'MD', 'Cash', 'Pending'),
('Vitamins', 'Medicine', 5, 10.00, 'Omaha', 'NE', 'Debit Card', 'Delivered'),
('Pain Relievers', 'Medicine', 3, 7.00, 'Long Beach', 'CA', 'Credit Card', 'In Progress');
#QUESTION;-
Sure, here are 10 questions to practice GROUP BY in SQL for the orders table:
4. *Find the state with the highest order value.*
5. *Find the payment mode with the highest average order value.*
6. *Find the delivery status with the highest average order value.*
7. *Find the city with the highest number of orders for each category.*
8. *Find the state with the highest number of orders for each payment mode.*
9. *Find the category with the highest average order value for each delivery status.*
10. *Find the payment mode with the highest average order value for each state.*
#Answer;-
#Q4:Find the state with their order value.
SELECT state_name, SUM(quantity * unit_price) AS order_value
FROM ecom
GROUP BY state_name;
#Q5.Find the payment mode with their average order value.
SELECT payment_mode, AVG(quantity * unit_price) AS avg_order_value
FROM ecom
GROUP BY payment_mode;
#Q6:Find the delivery status with their average order value.
SELECT delivery_status, AVG(quantity * unit_price) AS avg_order_value
FROM ecom
GROUP BY delivery_status;
#Q7:Find the city with their number of orders for each category.
SELECT ordered_city, category, COUNT(*) AS number_of_orders
FROM ecom
GROUP BY ordered_city, category;
#Q8: Find the state with their number of orders for each payment mode.
SELECT state_name, payment_mode, COUNT(*) AS number_of_orders
FROM ecom
GROUP BY state_name, payment_mode;
#Q9: Find the category with their average order value for each delivery status.
SELECT category, delivery_status, AVG(quantity * unit_price) AS avg_order_value
FROM ecom
GROUP BY category, delivery_status;
Data set:-4
Use da3;
CREATE TABLE movies (
movie_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(120) NOT NULL,
release_year INT NOT NULL,
director_name VARCHAR(120)
);
#dekhane ke liye
Select * from movies;
INSERT INTO movies (title, release_year, director_name) VALUES
('The Shawshank Redemption', 1994, 'Frank Darabont'),
('The Godfather', 1972, 'Francis Ford Coppola'),
('The Dark Knight', 2008, 'Christopher Nolan'),
('The Lord of the Rings: The Return of the King', 2003, 'Peter Jackson'),
('Pulp Fiction', 1994, 'Quentin Tarantino'),
('Schindler's List', 1993, 'Steven Spielberg'),
('The Silence of the Lambs', 1991, 'Jonathan Demme'),
('Forrest Gump', 1994, 'Robert Zemeckis'),
('Inception', 2010, 'Christopher Nolan'),
('The Matrix', 1999, 'The Wachowskis'),
('The Good, the Bad and the Ugly', 1966, 'Sergio Leone'),
('12 Angry Men', 1957, 'Sidney Lumet'),
('The Usual Suspects', 1995, 'Bryan Singer'),
('Fight Club', 1999, 'David Fincher'),
('The Big Lebowski', 1998, 'Joel and Ethan Coen'),
('The Good Will Hunting', 1997, 'Gus Van Sant'),
('The Lion King', 1994, 'Roger Allers and Rob Minkoff'),
('Spirited Away', 2001, 'Hayao Miyazaki'),
('The Departed', 2006, 'Martin Scorsese'),
('There Will Be Blood', 2007, 'Paul Thomas Anderson'),
('The Social Network', 2010, 'David Fincher'),
('The Parasite', 2019, 'Bong Joon-ho');
#QUESTION;-
1.Update the release_year column to 2023 for the movie "The Shawshank Redemption".
2.Update the director_name column to "Bong Joon-ho" for the movie "The Godfather".
3.Update the title column to "The Matrix: Resurrections" for the movie "The Matrix".
4.Update the director_name column to "David Fincher" for all movies that are directed by Christopher Nolan.
5.Delete all movies from the table that were released before 1990.
6.Delete all movies from the table that are directed by Steven Spielberg.
7.Rename the title column to movie_name.
8.Create a unique constraint on the movie_name column.
#Answer;-
-- 1. Update the release_year column to 2023 for the movie "The Shawshank Redemption".
UPDATE movies
SET release_year = 2023
WHERE title = 'The Shawshank Redemption';
-- 2. Update the director_name column to "Bong Joon-ho" for the movie "The Godfather".
UPDATE movies
SET director_name = 'Bong Joon-ho'
WHERE title = 'The Godfather';
-- 3. Update the title column to "The Matrix: Resurrections" for the movie "The Matrix".
UPDATE movies
SET title = 'The Matrix: Resurrections'
WHERE title = 'The Matrix';
-- 4. Update the director_name column to "David Fincher" for all movies that are directed by Christopher Nolan.
UPDATE movies
SET director_name = 'David Fincher'
WHERE director_name = 'Christopher Nolan';
-- 5. Delete all movies from the table that were released before 1990.
DELETE FROM movies
WHERE release_year < 1990;
-- 6. Delete all movies from the table that are directed by Steven Spielberg.
DELETE FROM movies
WHERE director_name = 'Steven Spielberg';
-- 7. Rename the title column to movie_name.
ALTER TABLE movies
CHANGE COLUMN title movie_name VARCHAR(120) NOT NULL;
-- 8. Create a unique constraint on the movie_name column.
ALTER TABLE movies
ADD CONSTRAINT unique_movie_name UNIQUE (movie_name);
Data set:-5
use db9;
CREATE TABLE customers (customer_id INT PRIMARY KEY AUTO_INCREMENT,first_name VARCHAR(50) NOT NULL,last_name VARCHAR(50) Not NULL,
email VARCHAR(100) NOT NULL UNIQUE,create_date DATE NOT NULL);
INSERT INTO customers (customer_id, first_name, last_name, email, create_date)
VALUES
(1, 'John', 'Doe', 'johndoe@example.com', '2023-11-15'),
(2, 'Jane', 'Smith', 'janesmith@gmail.com', '2023-10-16'),
(3, 'Peter', 'Jones', 'peterjones@gmail.com', '2023-07-17'),
(4, 'Mary', 'Brown', 'marybrown@example.com', '2023-09-18'),
(5, 'David', 'Williams', 'davidwilliams@example.com', '2023-12-19'),
(6, 'Sarah', 'Miller', 'sarahmiller@example.com', '2023-08-20'),
(7, 'Michael', 'Taylor', 'michaeltaylor@example.com', '2023-01-21'),
(8, 'Karen', 'Davis', 'karendavis@gmail.com', '2023-02-22'),
(9, 'Antonio', 'Lopez', 'antoniolopez@yahoo.com', '2023-11-23'),
(10, 'Patricia', 'Sanchez', 'patriciasanchez@gmail.com', '2023-11-24'),
(11, 'Robert', 'Wilson', 'robertwilson@example.com', '2023-10-25'),
(12, 'Susan', 'Rodriguez', 'susanrodriguez@yahoo.com', '2023-10-26'),
(13, 'James', 'Phillips', 'jamesphillips@hotmail.com', '2023-11-27'),
(14, 'Linda', 'Lee', 'lindalee@gmail.com', '2023-11-28'),
(15, 'Mark', 'Thomas', 'markthomas@rediff.com', '2023-11-29');
#QUESTION;-
1. Count the total number of customers created in November 2023.
2.Identify the customers who were created before October 1, 2023.
SQL
3.Calculate the average number of customers created per month in 2023.
4.Find the most recent customer creation date.
5.Determine the oldest customer in the table based on their creation date.
7.Display a column of month form given table
8.display the customer name, who purchased on "sunday"
9.Display the name of customer who created on October month
10.Display the first name, last name, email, and creation date for customers whose email addresses end with "@yahoo.com".
#Answer;-
#dekhane ke liye
select * from customers;
#1. Count the total number of customers created in November 2023.
SELECT COUNT(*) AS total_customers_in_november FROM customers WHERE create_date >= '2023-11-01' AND create_date <= '2023-11-30';
#2. Identify the customers who were created before October 1, 2023.
SELECT COUNT(*) AS total_customers_in_october FROM customers WHERE create_date <= '2023-10-01';
#3. Calculate the average number of customers created per month in 2023.
SELECT AVG(COUNT(*)) AS avg_customers_per_month
FROM customers
WHERE create_date >= '2023-01-01' AND create_date <= '2023-12-31'
GROUP BY MONTH(create_date);
#4.Find the most recent customer creation date
SELECT MAX(create_date) AS most_recent_creation_date
FROM customers;
#5.Determine the oldest customer in the table based on their creation date.
SELECT min(create_date) AS oldest_creation_date FROM customers;
#6.Display the first name, last name, email, and creation date for customers whose email addresses start with "J".
SELECT first_name, last_name, email, create_date FROM customers WHERE email LIKE 'J%';
#7.Display a column of month form given table
select *,monthname(create_date) from customers;
#8.display the customer name, who purchased on "sunday"
select *,DAYNAME(create_date) from customers where DAYNAME(create_date)="Sunday";
#9.Display the name of customer who created on October month
select *,first_name,last_name from customers where year(create_date) and month(create_date)=10;
#10.Display the first name, last name, email, and creation date for customers whose email addresses end with "@yahoo.com".
select *,first_name,last_name,email from customers where email like "%@yahoo.com
Table:- 1
_____x____x_____x______x______x_____x____x______x______x
Create table customerinfo(cid int not null auto_increment primary key,
Cname varchar(40) not null,
Age int not null);
Select *from customerinfo;
Insert into customerinfo(cname,age)
Values
("ram",12),
("shyam",16),
("mohan",22),
("krishna",25);
customerinfo
_____x________x________x_______x________x_______x_______
Table:- 2
Create table orders(oid int not null auto_increment primary key,
Product varchar(40) not null,
Odate date not null,
Qty int not null);
#FOREIGN KEY (column_name) REFERENCES #referenced_table_name(referenced_column_name)
#);
Foreign key(cid) reference customerinfo(cid));
Select * from orders;
insert into orders(product,odate,qty)
values
("shart",'2005-10-8',7),
("shoe",'2023-12-1',7),
("watch",'2023-10-9',8),
("tie",'2023-1-20',6);
Orders
1.INNER JOIN:
syntax:-SELECT * FROM table1
INNER JOIN table2 ON table1.column = table2.column;
Join
Types of join
Four types of join
1.INNER JOIN:
syntax:-SELECT * FROM table1
INNER JOIN table2 ON table1.column = table2.column;
2.LEFT JOIN (or LEFT OUTER JOIN):
syntax:-SELECT * FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
3.RIGHT JOIN (or RIGHT OUTER JOIN):
syntax:-SELECT * FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
4.CROSS JOIN:
syntax:-SELECT * FROM table1
CROSS JOIN table2;
foreign key
#____________________Table 1: joint___________________________________
create database joint;
use joint;
create table customerinfo(cid int not null auto_increment primary key,
cname varchar (250) not null,
age int not null);
select * from customerinfo;
INSERT INTO customerinfo(cname, age)
VALUES
('John Doe', 30),
('Jane Doe', 25),
('Peter Jones', 40),
('Mary Smith', 22),
('David Brown', 50),
('Sarah Williams', 35),
('James Miller', 18),
('Patricia Taylor', 60),
('Michael Johnson', 45),
('Elizabeth Wilson', 28);
#_____________________Table2:orders________________________________
create table orders(oid int primary key not null ,
product varchar(250) not null,
odate date not null,
qty int not null,
cid int not null,
foreign key(cid) references customerinfo(cid));# foreign key
#___________dekhane ke liye_____________
select * from orders;
#_____________________________________
INSERT INTO orders (oid, product, Odate, Qty, cid)
VALUES
(1, 'Laptop', '2023-10-04', 3, 3),
(2, 'Phone', '2023-10-04', 9, 4),
(3, 'Tablet', '2023-10-04', 3, 6),
(4, 'TV', '2023-10-04', 8, 7),
(5, 'Refrigerator', '2023-10-04', 9, 1),
(6, 'Washing Machine', '2023-10-04', 8, 2),
(7, 'Microwave', '2023-10-04', 4, 7),
(8, 'Air Conditioner', '2023-10-04', 3, 4),
(9, 'Vacuum Cleaner', '2023-10-04', 2, 10),
(10, 'Coffee Maker', '2023-10-04', 6, 4);
#___________join karne ke liye_______________________
select * from customerinfo join orders
on customerinfo.cid=orders.cid;