Essential SQL Commands: Query, Modify, and Manage Data

Posted on Feb 27, 2025 in Computers

Frequently Used SQL Commands

DROP (Delete a Table)

DROP TABLE table_name;

DROP TABLE IF EXISTS table_name; (Checks if the table exists before deleting)

DELETE (Delete Records)

DELETE FROM table_name WHERE field_name = "value";

Example: DELETE FROM table_name WHERE field_name = "delete to juan:";

WHERE Clause

SELECT * FROM table_name WHERE field_name > 1000;

AND and OR Operators

SELECT * FROM table_name WHERE field_name > 1000 OR (field < 500 AND field > 275);

BETWEEN Operator

SELECT * FROM table_name WHERE date_field BETWEEN '2009-06-06' AND '2009-06-10';

LIKE Operator

Find records where a field contains a specific pattern.

SELECT * FROM table_name WHERE field_name LIKE '%AN%'; (Finds records where field_name contains ‘AN’)

ORDER BY Clause

Sort results by a specific field.

SELECT * FROM table_name ORDER BY field_name DESC;

Example: SELECT * FROM suppliers ORDER BY name DESC;

Multiple fields:

SELECT field1, field2, field3 FROM table_name ORDER BY field_name ASC; (Ascending order)

SELECT field1, field2, field3 FROM table_name ORDER BY field_name DESC; (Descending order)

SQL IN Operator

Select records that match any value in a list of values.

SELECT * FROM table_name WHERE field_name IN ('value1', 'value2');

SQL JOIN Operator

Combine rows from two or more tables based on a related column.

Example: Selecting customer information with commune details:

SELECT customers.name, customers.rut, commune.comuna.codigo FROM customers, commune WHERE description = "001";

Simple SELECT Statement

Retrieve all fields from a table:

SELECT * FROM table_name;

Selecting Specific Fields

Retrieve only certain fields from a table:

SELECT field1, field2 FROM table_name;

Selecting a Specific Record

Retrieve a specific record based on a condition:

SELECT * FROM table_name WHERE RUT = "80000000";

LIKE Clause (Pattern Matching)

Find records where a field begins with a certain value:

SELECT * FROM table_name WHERE address LIKE "AV%";

IN Clause (Multiple Options)

Retrieve records where a field matches one of several options:

SELECT * FROM table_name WHERE invoice_number IN (102, 103);

BETWEEN Clause (Range)

Retrieve records where a field falls within a given range:

SELECT * FROM table_name WHERE invoice_number BETWEEN 102 AND 118;

COUNT Records

Count all records in a table:

SELECT COUNT(*) AS total FROM customers;

SUM Function

Calculate the sum of values in a field:

SELECT SUM(value_field) FROM table_name;

AVG Function

Calculate the average value of a field:

SELECT AVG(value_field) FROM table_name;

MAX Function

Find the maximum value in a field:

SELECT MAX(value_field) FROM table_name;

MIN Function

Find the minimum value in a field:

SELECT MIN(value_field) FROM table_name;

DROP Database

Delete a database:

DROP DATABASE database_name;