SECTION 2: SQL COMMANDS

As mentioned earlier, SQL used specific commands which control the tables within database systems, especially when handling multiple tables with big data. Each table is often defined by a given name, such as customers or orders. It should be known that all functions or commands are done with SQL statements. More so, SQL statements, as well as keywords, are not case sensitive, and some data bases may require semicolons on statements as a standard format. Therefore, SQL statements include SELECT, UPDATE, DELETE, INSERT INTO, and more. 

SELECT

Like most computer commands, SELECT is primarily utilized for querying the database as well as in the retrieval of intended data that matches the parameters specified. For example, SELECT column1 [, column2, …], FROM (table name), WHERE (condition). When writing the SELECT command, you may use conditional clauses such as equal (=), greater than (>), less than (<), greater than or equal (>=), less than or equal (<), not equal to (<>) and LIKE. When you write SELECT * FROM (table_name), it readily returns the entire data from the table and may, at times, include quotes around the text. However, do not use quotes to enclose mathematical objects. When using LIKE, ensure it matches a pattern, especially when involving the use of (%), which denotes 0 in SQL programming. For example, ‘A%’ resembles all strings which use A as the first letter while ‘%a’ matches data, which ends with a. Similarly, ‘%a%’ matches strings or data which have an ‘a’ between values or information in a given table or database.

UPDATE

When there are preexisting values in a table and there in need to change them, then the UPDATE command is used. The command uses the functions UPDATE (table_name), SET colX=valX [, colY=valY,… ], WHERE (condition). This type of SQL command is applicable when there is a need to change, alter, or edit rows in a given table, therefore, creating a more significant process of the management of database systems. At some point, specific rows can be modified without causing any impact to data in a similar column.

DELETE

As the name suggests, this is a term used in computing for the removal of unwanted, repeated, or unused data from the system. Similarly, SQL also utilizes a similar function as a command to remove unwanted records from a given table within the system. In this case, it uses the feature DELETE FROM (table_name), WHERE (conditions), and the file will be deleted successfully. You can also clear the entire information without getting rid of the table using the function DELETE * FROM (table_name). Therefore, when removing the record, the specified range or conditions set determines the extent of what you are getting rid of. However, some conditions may compromise the data if you delete vital data due to wrongly and unspecified parameters.

INSERT

When the table has been created, that is, the columns and rows are specified as required, data is then fed into the system. However, it is usually challenging to feed information one at a time in each column and row more so when the data is bulky. This may result in errors that may go unnoticed, therefore compromising the future. Therefore, SQL uses the INSERT VALUE, also referred to as the INSERT INTO command, to feed data into the table and at required columns and rows. The programming language uses the function INSERT INTO (table_name), ( col1,…….., coln), VALUES (val1,….,valn) to insert values. The ‘n’ denotes the number of the last column in your table where the previous value will be added. In this case, inserting the values into the table becomes straightforward quicker and prevents cases emerging from sparse data entry formats.

Create Table

The CREATE TABLE clause is another basic command of SQL essential for developing a table to insert the information needed. The function used is CREATE TABLE (table_name), (column1 data type, column2 data type, column3 data type,…). When creating a table, there are also other considerations, which include char for which the fixed-length character string, varchar also size, number with a maximum column value and data value. As database systems comprise of several tables, you can add more tables in a certain database and insert your values.

Alter Table

In some cases, you may have created a table with either more or fewer columns hence the need to delete or add. Therefore, SQL provides a query that enables you to quickly remove or columns to an existing table by first specifying a data type along with the number of columns to add or delete. The format used in this case is ALTER TABLE (table_name), DROP COLUMN colname. That is, specify the table name in which you are to add or delete columns and then set the parameters for which data type and on which column you wish to modify.

Drop Table

Another necessary command used in SQL is for getting rid of the entire table from the database when it is unwanted of there is a need to create a new one. That is, SQL allows you to use the command DELETE * FROM (table_name) and drop a given table. As a basic command in SQL, DROP TABLE is crucial, especially when you want to have another table with fresh details, which differs significantly when compared to the previous one.

Create Index & Drop Index

Another typical command in SQL is for creating indexes also essential in databases. When you want to create an index, it involves developing a search key that facilitates the process of searching and retrieving data quickly. Like the DROP TABLE command, DROP INDEX means deleting of an existing index from the database and the development of a new one.

Create View

As a result of an SQL SELECT, CREATE VIEW is another command or query that entails a virtual table but with fields from more than one table in a database system. As it has similar values like that of the original table, view tables are meant to provide a preview of the information stored in one or more tables within a database. Therefore, a view or virtual table can be considered a real table. The function used is CREATE TABLE view_name AS, SELECT col_name(s), FROM table_name, WHERE (condition). View table can be utilized within a command, saved procedures, and from other similar commands. As such, this facilitates the process of adding more functions and linking them, therefore, presenting the necessary data to the user.

Group By

The GROUP BY is a much newer command added to facilitate the process of grouping aggregate results searched by column values. In other words, GROUP BY entails enabling the database to group data with respect to the similarities highlighted by the user into a given column. The format used included; SELECT col_name, function (col_name) FROM table_name GROUP BY col_name. However, the ‘WHERE’ keyword was replaced in this command with ‘HAVING,’ which brings out the condition. Therefore, the function used is SELECT col_name, function (col_name) FROM table_name, GROUP BY col_name, HAVING function (col_name) conditions data. In this case, the user is, therefore, capable of grouping columns according to their requirements.

Join

Data may be spread among different tables within a single database; hence, you wish to collate these data into one table. Tables under a single database typically have a connection through the keys, therefore, making it easy to associate the data between different tables. As such, the tables can be linked together through the process of join in SQL programming. For example, if you have three tables with a connection such as customers, orders, and product supply, then you can join them using the JOIN command. As such, use the command SELECT customer_name, product name, FROM customer_name, order, product supply, WHERE customer = orders = product supply. Besides, the command also allows for INNER JOIN for the selection of two tables and LEFT JOIN to return all the rows to the initial table.

Create Database and Alter Database

When using the SQL programming tool, you may choose to create more than one database using the necessary command of CREATE DATABASE. The command quickly incorporates a fresh database where an individual can develop and design several tables within. Most often, you may create a database and then go ahead to make changes and modify the tables as well. As to modify the SQL database, you may use the statement ALTER DATABASE which readily modifies the system.

Other SQL Commands

ORDER BY is another clause that orders column names according to user needs using the command ORDER BY col_name ASC for ascending and DESC for descending. There is also IN, AND/OR, BETWEEN and AND. All these functions are essential for operating values in the table. For instance, IN is crucial for returning for a known value using the format SELECT * FROM table_name WHERE col_name IN (val1, val2,…). BETWEEN and AND are vital for providing a range between two values and use the function SELECT * FROM table_name WHERE col_name BETWEEN val1 AND val2. AND/OR may be used to join two or more values or conditions and uses either WHERE, AND, and OR formats to determine its viability.

<<<PREVIOUS SECTION||||||||||||||||||||||||||||NEXT SECTION>>>