MySql Commands
• SELECT -Retrieve data from a database.
Eg:-SELECT * FROM customers;
• INSERT - Insert new records into a table.
Eg:- INSERT INTO customers VALUES ('John', 30);
• UPDATE- Modify existing records in a table.
Eg:-UPDATE customers SET age = 31 WHERE name = 'John';
• DELETE -
Eg:- DELETE FROM customers WHERE name = 'John';
• CREATE TABLE - Create a new table in a database.
◦ Eg:- CREATE TABLE customers ( id INT PRIMARY KEY, name
VARCHAR(100), age INT );
• DROP TABLE :-Delete a table.
Eg:- DROP TABLE customers;
• ALTER TABLE :-Modify the structure of an existing table.
1. Add coloumns
Eg:- ALTER TABLE customers ADD email VARCHAR(100);
Adds a new column "email" to the "customers" table.
2. Delete coloumn
Eg:-ALTER TABLE customers DROP COLUMN email;
Removes the "email" column from the "customers" table.
3. Modify an existing coloumn
1. Modify an existing column
ALTER TABLE table_name MODIFY column_name
new_column_datatype;
Eg:-ALTER TABLE users MODIFY email VARCHAR(500);
2. Rename a column
ALTER TABLE table_name CHANGE old_column_name
new_column_name column_datatype;
Eg:- ALTER TABLE users CHANGE email email_address
VARCHAR(200);
• ORDER BY :-Sort the result set.
1. Descending oder(Highest Order)
Eg:- SELECT * FROM customers ORDER BY age DESC;
2. Ascending oder(Alaphabetical Order)
Eg:- SELECT * FROM customers ORDER BY age ASC;
OR
SELECT * FROM customers ORDER BY age;
AGGREGATE FUNCTIONS
In a Database Management System (DBMS), aggregate functions are used to perform
calculations on multiple rows of data to return a single result. They are commonly
used with the GROUP BY clause to summarize data.
• COUNT() :- Returns the number of rows that match a specified condition.
Eg:- SELECT COUNT(*) FROM employees;
Counts the total number of rows in the "employees" table.
• SUM() :- Returns the sum of values in a numeric column.
Eg:- SELECT SUM(salary) FROM employees;
• AVG() :- Returns the average value of a numeric column.
Eg:- SELECT AVG(salary) FROM employees;
• MAX() :-Returns the maximum value in a column.
Eg:- SELECT MAX(salary) FROM employees;
• MIN() :-Returns the minimum value in a column.
Eg:- SELECT MIN(salary) FROM employees;
Q. Write the SQL queries for the below questions.
Table:- employee
Empid Name Age Salary
1 Suman 36 20000
2 Sanjay 42 32000
3 Ravi 30 10000
4 Hari 55 5500
1. Display the above table.
select * from employee;
2. To display all the records in the alphabetical order of name.
select * from employee order by Name asc;
OR
select * from employee order by Name;
3. To display the name and salary in the highest order of salary.
select Name,Salary from employee order by Salary desc;
4. Display the employee id and name.
select Empid, Salary from employee;
5. Display the name and salary whose salary between 8000 and 30000.
select Name,Salary from employee where salary between 8000 and 30000;
6. Display all the details of employee whose age greater than 40.
Select * from employee where age>40;
7. Insert a new record with value: 5, 'Sam' , 68, 45000.
insert into employees values (5, 'Sam' , 68, 45000);
8. Change the salary of the employee to 35000 whose id=1.
update employee set salary=35000 where id=1;
9. Delete the coloumn Age from the table.
alter table employee drop coloumn Age;
10.Add a new coloumn city.
alter table employee add coloumn city varchar(100);
11.Delete employee whose id=3;
delete from employee where id=3;
12.Delete the entitre table.
drop table employee;
___________
