SQL
Example Table Creation and View Management
Create the employees table:
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);
Insert sample data into the employees table:
INSERT INTO employees (first_name, last_name, department, salary)
VALUES
('John', 'Doe', 'Engineering', 70000),
('Jane', 'Smith', 'Marketing', 80000),
('Alice', 'Johnson', 'Engineering', 75000),
('Bob', 'Brown', 'HR', 60000),
('Tom', 'Harris', 'Marketing', 85000);
Create a view to show Engineering employees:
CREATE VIEW engineering_employees AS
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Engineering';
Query the view:
SELECT * FROM engineering_employees;
Create a view with aggregation:
CREATE VIEW department_salaries AS
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
Query the aggregated view:
SELECT * FROM department_salaries;
Update data through a view (if updatable):
UPDATE engineering_employees
SET salary = salary + 5000
WHERE last_name = 'Doe';
Modify an existing view:
CREATE OR REPLACE VIEW engineering_employees AS
SELECT first_name, last_name, salary, department
FROM employees
WHERE department = 'Engineering';
Drop a view:
DROP VIEW engineering_employees;
General Tips!
- Use Proper Indexing: Index frequently queried columns to improve query performance, but avoid over-indexing as it can slow down write operations.
- Write Efficient Queries: Optimize your queries by selecting only the necessary columns, avoiding SELECT *, and using joins wisely.
- Normalize Your Data: Organize your database into normalized tables to reduce redundancy and improve data integrity.
- Use Transactions: Ensure data consistency and integrity by wrapping multiple related operations in transactions, especially in multi-step processes.
- Understand and Use SQL Functions: Leverage built-in SQL functions for data manipulation and retrieval, such as aggregate functions (SUM, COUNT) and string functions (CONCAT, SUBSTRING).