ssh by23ai001@172.16.6.
30
pw: 1by23ai001
sudo mysql
pw: 1by23ai001
1) Show databases;
2) Create Database USN;
3) Use USN;
4) CREATE TABLE Student( USN VARCHAR(20) PRIMARY
KEY, NAME CHAR(25) NOT NULL, DEPT VARCHAR(25) NOT
NULL, SEM VARCHAR(20) NOT NULL, Phone_NO LONG
INT(20) NOT NULL);
5) DESC Student;
6) INSERT INTO Student(USN,NAME,DEPT,SEM, Phone_NO
) VALUES(‘24BY022’,’XYZ’,AIML’,’6TH ‘, 777777);
7) Select *from Student;
USN STUDENT_NAME DEPT SEM PHONE_NO
1BY20AI01 22222
1BY20AI02 33333
1BY20AI03 12345
1BY20AI04 55555
1BY20AI05 66666
1)Display all content from table
2)Display only usn, name, dept from student table
SELECT USN, NAME, DEPT From STUDENT;
4) Update phone number of 1BYAI03 FROM 12345 TO 56780
Update Student SET Phone_no=“56780” where USN=“1BYAI03”;
5) Insert new row to the table STUDENT with all attributes with usn
1BYAI08
6) Delete the row with USN 1BYAI08
DELETE FROM Student where USN=“1BYAI08”;
7) Add a new column Section to the student table
Alter Table Student ADD Section char(20);
8) Alter Table Student DROP Column Section;
9) ALTER TABLE Student RENAME Column
Name TO Student_name;
10)Alter Table Student CHANGE NAME
Student_Name char(20);
11)Update Student SET Section=“A” where
USN=“1BYAI03”;
12)Alter Table Student MODIFY Column DEPT
Varchar(20);
Create database ai128_megha;
use ai128_megha;
Create customer table with attributes
Insert into Customers(CustomerID, CustomerName,ContactName,
Address……….)Values(1,’alfred’,’maria’,……..),(2,’ana’,…);
1) Display CustomerName, City from cuatomer table
2) SELECT DISTINCT Country from customer table
3) Display Customer name value By Ascending order
4) Select all customers from Spain that starts with the letter ‘A’
5) Update city of customer Alfreds Futterkiste to ‘Frankfurt’;
6) Delete the row with customer name Alfreds Futterkiste.
7) Add new column Price to customer table
8) Update the values for price column using update command
9) Find the lowest price in the Price column
10) Alter the column name PostalCode to Postal_code.
11) Drop the column Price from Customer table.
1)SELECT CustomerName, City FROM Customers;
2) SELECT DISTINCT Country FROM Customers;
3) SELECT * FROM Customer ORDER BY CustomerName;
4)SELECT *FROM Customers
WHERE Country= ’Mexico' AND CustomerName LIKE ’A%’;
5) UPDATE Customers
SET City= 'Frankfurt'WHERE CustomerID = 1;
6) DELETE FROM Customers WHERE CustomerName='Alfreds
Futterkiste’;
7) ALTER TABLE Customers ADD Price integer(255);
9) SELECT MIN(Price) FROM Customer;
10) ALTER TABLE Customer RENAME COLUMN PostalCode
to Postal_code;
11) ALTER TABLE Customer DROP COLUMN Price;
1) Create table orders with attributes order_id, customerID, ProductName
CREATE TABLE orders (
2) Insert values to Orders table
order_id INT PRIMARY KEY,
customer_id INT,
product_name VARCHAR(50),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID);
INSERT INTO orders (order_id, customer_id, product_name) VALUES
(1234, 1, 'Laptop'),
(5678, 2, 'Smartphone');
Create table orders(order_ID int(20) primary key NOT NULL,CustomerID INT(20),Product_name CHAR(20) NOT
NULL, FOREIGN KEY(CustomerID) REFERENCES Customer(CustomerID)):
Insert into Orders(order_ID,CustomerID,Product_name)values(111, 2, “phone”),(222,3,”TV”));
1) write a SQL query to locate the orders made by customers. Return order number, customer ID
SELECT orders.order_id, customer.customerID FROM orders, customer WHERE
orders.customerId = customer.customerId;
2) 2. Find orders for a specific customer
SELECT *
FROM orders
WHERE customer_id = 101;
3)Get all orders with customer names
SELECT
o.order_id,
o.customerId,
o.product_name,
c.CustomerName
FROM orders o, INNER JOIN customer c ON c.customerId = o.customerId;
4) List all customers who have placed orders
SELECT DISTINCT c.customerId, c.customerName
FROM customers c
INNER JOIN orders o ON c. customerId = o. customerId;
5) Get all products ordered by customers named “Alfred Futterkiste“
SELECT o.product_name
FROM orders o
INNER JOIN customers c ON o.customeriId = c.customerId
WHERE c.CustomerName =“Alfred Futterkiste”;
Select distinct c.CustomerName from Customer c
Left join Orders o
On o.Customer_id = c.Customer_id
Where order_id is null
Order by c.CustomerName;
