Scribd
Upload
2 views3 pages

Mysql SQL Practice Examples

This document provides a comprehensive guide on MySQL commands and queries for database management, including creating databases and tables, inserting and manipulating data, and performing various SQL operations. It covers essential SQL functions such as SELECT, UPDATE, DELETE, and JOIN, along with examples for each command. Additionally, it includes instructions for using aggregate functions, sorting, limiting results, and creating views.

Uploaded by

Mark Bangayan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views3 pages

Mysql SQL Practice Examples

This document provides a comprehensive guide on MySQL commands and queries for database management, including creating databases and tables, inserting and manipulating data, and performing various SQL operations. It covers essential SQL functions such as SELECT, UPDATE, DELETE, and JOIN, along with examples for each command. Additionally, it includes instructions for using aggregate functions, sorting, limiting results, and creating views.

Uploaded by

Mark Bangayan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

MySQL SQL Practice Examples (For MySQL Workbench)

1. Database Commands
CREATE DATABASE company_db;

SHOW DATABASES;

USE company_db;

DROP DATABASE company_db;

2. Create Tables
CREATE TABLE employees (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age INT,
department VARCHAR(50),
salary DECIMAL(10,2)
);

CREATE TABLE departments (


dept_id INT AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(100)
);

3. Insert Data
INSERT INTO employees (name, age, department, salary)
VALUES ('Juan Dela Cruz', 25, 'IT', 25000);

INSERT INTO employees (name, age, department, salary)


VALUES
('Maria Santos', 23, 'HR', 22000),
('Pedro Reyes', 28, 'Finance', 27000),
('Ana Lopez', 26, 'IT', 26000);

4. Select Queries
SELECT * FROM employees;

SELECT name, department FROM employees;

SELECT * FROM employees WHERE department='IT';

SELECT * FROM employees WHERE salary > 25000;


5. Update Queries
UPDATE employees
SET salary = 30000
WHERE emp_id = 1;

UPDATE employees
SET department = 'Management'
WHERE name = 'Maria Santos';

6. Delete Queries
DELETE FROM employees WHERE emp_id = 3;

DELETE FROM employees WHERE department='Finance';

7. Sorting and Limiting


SELECT * FROM employees ORDER BY salary ASC;

SELECT * FROM employees ORDER BY salary DESC;

SELECT * FROM employees LIMIT 5;

8. Aggregate Functions
SELECT COUNT(*) FROM employees;

SELECT AVG(salary) FROM employees;

SELECT SUM(salary) FROM employees;

SELECT MAX(salary) FROM employees;

SELECT MIN(salary) FROM employees;

9. Conditions
SELECT * FROM employees WHERE age BETWEEN 22 AND 27;

SELECT * FROM employees WHERE name LIKE 'A%';

SELECT * FROM employees WHERE department IN ('IT','HR');

10. Group By
SELECT department, COUNT(*) AS total
FROM employees
GROUP BY department;

SELECT department, AVG(salary) AS average_salary


FROM employees
GROUP BY department;
11. Join Examples
SELECT [Link], departments.dept_name
FROM employees
INNER JOIN departments
ON [Link] = departments.dept_name;

SELECT [Link], departments.dept_name


FROM employees
LEFT JOIN departments
ON [Link] = departments.dept_name;

12. Alter Table


ALTER TABLE employees ADD email VARCHAR(100);

ALTER TABLE employees MODIFY salary DECIMAL(12,2);

ALTER TABLE employees DROP COLUMN age;

13. Table Management


SHOW TABLES;

DESCRIBE employees;

TRUNCATE TABLE employees;

DROP TABLE employees;

14. Subqueries
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

15. Views
CREATE VIEW employee_view AS
SELECT name, department, salary
FROM employees;

SELECT * FROM employee_view;

You might also like