Learned the Part I of SQL Preliminary Basics
To insert data to your table, execute the following statement:
INSERT INTO [table_name] VALUES (...data);
Where ...data is the comma-separated list of arguments (values) in order corresponding to the columns of the table. Again, the trailing comma at the last value must be omitted.
Example: 1, 'John', 'Doe', 22
You can also insert data with specified columns only if neglected columns allow null values or have a DEFAULT constraint. It can be achieved by executing this SQL statement:
INSERT INTO [table_name] (...columns) VALUES (...data);
Where ...columns is the comma-separated list of names of specified columns.Â
Example: id, firstname, lastname, age
The arrangement of the arguments from ...data must correspond to the statement’s specified columns, which are defined inside the parentheses.
We can retrieve the data from the table to make sure that the data is inserted successfully. To retrieve all the data from the table, execute the following query:
SELECT * FROM [table_name];
This will display all the columns from the specified table, but you can specify necessary columns to display in the output.
To only display specified columns from a table:
SELECT ...columns FROM [table_name];
Oftentimes, when retrieving data, we only need the necessary records to gather and use them in some other statements, queries, or subqueries.
SELECT * FROM users WHERE id <= 5;
In this query, it retrieves some of the data which has an ID of less than or equal to 5. The expression beside the WHERE keyword can be any condition or boolean expression. Thus, adding a WHERE clause to the query will extract only some of the data that satisfy the corresponding condition.
Other examples:
SELECT * FROM users WHERE id <= 5;
SELECT id, first_name, last_name FROM users WHERE picture_filename IS NULL;
SELECT first_name FROM users WHERE last_name LIKE 'P%';
Include the condition with the rownum pseudocolumn in the WHERE clause to limit the number of rows returned by a query. The following example for it is shown below:
SELECT * FROM users WHERE rownum <= 10;
This returns the first ten records from the users table. Only use < or <= operators, alternatively > or >= operators if number is the left operand. Otherwise, it would not work.
To update data from a table, use the following syntax:
UPDATE [table_name]
SET column_name = new_value
WHERE [condition];
Where [table_name] is the name of the table where some of the data will be updated. column_name is the name of the column used to locate any data to update, and new_value is the value that replaces the old values of selected columns, depending on the condition. condition is the boolean-returning expression, which is responsible for selecting data to update.
If the query doesn't have any condition, it will update all the records, which affects all the data from the specified columns.
Delete specific data with the corresponding condition:
DELETE FROM [table_name] WHERE [condition];
If the condition is not specified, it will delete all the data. To ensure deletion of small amount of data, always incorporate the condition to narrow the scope of data selection.
This query will sort the data fetched in ascending alphabetical order.
SELECT * FROM users
ORDER BY first_name;
To sort in descending order:
SELECT * FROM users
ORDER BY first_name DESC;
To add a column to a table, use the ALTER TABLE statement and specify the table. Put the ADD keyword beside the table name, then define its attributes as if you're defining it in the CREATE TABLE statement.
ALTER TABLE users ADD name VARCHAR(100) NOT NULL;
To delete a column in a table, use the same statement, followed by the DROP COLUMN clause, and finally specify the target column.
ALTER TABLE users DROP COLUMN name;
If you want to change the attributes of a particular column, use the ALTER TABLE statement. The MODIFY clause follows the statement and followed by a column name and the new attributes of the corresponding column.
ALTER TABLE users MODIFY name VARCHAR(100) NOT NULL UNIQUE;
To rename a column in a table, use the following syntax:
ALTER TABLE [table_name] RENAME COLUMN [column_name] TO [new_name];
For efficient learning, consider visiting this link: https://www.w3schools.com/sql/default.asp