https://www.youtube.com/watch?
v=STb1OEdnULo&list=PLGqhbs3HqjBYu743MKL0WfhG4ChQjLo8m&index=4
https://www.mediafire.com/file/g7ehdtxqgv29y7e/mysql-workbench.zip/file
###################################################################################
############
create database University
drop database University
use University
create table Student
(
ID int,
Name varchar(15),
City varchar(10)
)
drop table Student
create table Student
(
ID int primary key auto_increment,
Name varchar(15) not null,
City varchar(10) default 'Asyut'
)
select * from Student
insert into Student (ID,Name) values (1,'Reham')
insert into Student values (2,'Ahmed','Alex')
insert into Student values (3,'Mohammed','Alex'),
(4,'Mona','Cairo');
alter table Student add column Age int;
update Student set Age=25;
update Student set Age=27 where City ='Alex';
delete from Student where Name = 'Mona' ;
###################################################################################
############
CREATE DATABASE Store;
Use Store;
create table Product
(
PName CHAR(30),
Price float,
Category CHAR(30),
Manufacturer CHAR(30)
)
insert into Product values ('Gizmo',19.99,'Gadgets','GizmoWorks'),
('Powergizmo',29.99,'Gadgets','GizmoWorks'),
('SingleTouch',149.99,'Photography','Canon'),
('MultiTouch',203.99,'Household','Hitachi')
SELECT *
FROM Product
WHERE Category='Gadgets'
select PName,Price,Manufacturer
from Product
where Price>100
select Category
from Product
select Distinct Category
from Product
SELECT pname, price, manufacturer
FROM Product
WHERE price > 50
ORDER BY price, pname
update Product set price=203.99 where pname='SingleTouch'
SELECT DISTINCT category
FROM Product
ORDER BY category
SELECT Category
FROM Product
ORDER BY PName
SELECT DISTINCT category
FROM Product
ORDER BY PName
create table Company
(
CName CHAR(30) primary key,
StockPrice float,
Country CHAR(30)
)
insert into company values ('GizmoWorks',25,'USA'),
('Canon',65,'Japan'),
('Hitachi',15,'Japan')
alter table Product add foreign key (Manufacturer) references Company (CName)
SELECT PName, Price
FROM Product, Company
WHERE Manufacturer=CName AND Country='Japan' AND Price <= 200
create table Purchase
(
Product CHAR(30),
Date Date,
price float ,
Quantity int
)
insert into Purchase values ('Bagel','2022/10/21',1,20),
('Banana','2022/10/3',0.5,10),
('Banana','2022/10/10',1,10),
('Bagel','2022/10/25',1.5,20)
SELECT Sum(price * quantity)
FROM Purchase
WHERE product = 'bagel'
SELECT product, Sum(price*quantity) AS TotalSales
FROM Purchase
WHERE date > '2022/10/1'
GROUP BY product
SELECT product, sum(price * quantity) AS SumSales, max(quantity) AS MaxQuantity
FROM Purchase
GROUP BY product
SELECT product, Sum(price * quantity)
FROM Purchase
WHERE date > '2022/10/1'
GROUP BY product
HAVING Sum(quantity) > 30
select * from product;
select * from Purchase;
insert into Product values ('Bagel',19.99,'Gadgets','GizmoWorks')
delete from PRoduct where pname = 'Bagel'
UPDATE PRODUCT
SET price = price/2
WHERE Product.pname IN
(SELECT product
FROM Purchase
WHERE Date ='2022/10/25');
