Structured Query Language (or SQL) is a computer language used to execute transactions between the client and the database, resembling the means for communication between the end user and digital storage. It is used to store, manipulate and retrieve data from the database (acc. to W3Schools).
Run sqlplus on your terminal and connect to the database using the user credentials (Username: ict12a, Password: prog3113).
Keyword - It is a reserved word that has a particular purpose.
Statement - It is a combination of various keywords and values that make up an entire set of instructions for the database. These are ended using a semicolon (;).
Query - Same as statement, but it starts with the SELECT keyword.
Record - A row containing a set of corresponding values from the table columns.
SQL is classified into following three types:
DCL (Data Control Language) – It is used to manage user's privileges by granting or revoking privileges. It includes the keywords GRANT and REVOKE.
DDL (Data Definition Language) – It is used to provide and manage the underlying structure for the data. It includes the keywords CREATE, DROP, and TRUNCATE.
DML (Data Manipulation Language) – It is used to manage or alter the data from the database. It includes the keywords INSERT, SELECT, UPDATE, and DELETE.
These are the data types that are commonly used:
INT – A standard numeric data type that stores both positive and negative numbers without a decimal point.
VARCHAR(n) – A standard character-based data type that stores a series of characters with the specified capacity (n). Capacity is the maximum number of characters that can be stored within a string. Length is the number of characters that are present within the string.
NUMBER –A numeric data type that stores integers and floating-point numbers.
DATE – A data type that stores any valid date containing the year, month, and day.
To create a table, here is the following syntax for creating a table:
CREATE TABLE [table_name] (
column_name datatype constraints,
...
);
Where [table_name] is the name of the table, column_name is the column’s name, datatype is the column’s data type, and constraints can be NOT NULL, UNIQUE (or both with NOT NULL), or PRIMARY KEY. Ellipsis indicates that you can define more columns as you want. Make sure to omit the trailing comma at the definition of your last column.
There are more constraints other than mentioned above, and these will be discussed in a separate article.
To view the definition of the table, run the following line of SQL:
DESC [table_name]
It shows all the columns defined in the specified table. Each row in the output represent a column in the specified table and its attributes. While the columns in the output display the attributes, such as the name, the data type, and if a column allows the insertion of null values.
To drop—meaning to eliminate together with its data—a table. Run this line of SQL:
DROP TABLE [table_name];
Truncating a table do the same as deleting everything from the table, except for the following: you cannot roll back or recover the data within the targeted table. This process is considered the same as dropping and re-creating the table. To truncate a table, type this following SQL and run it.
TRUNCATE TABLE [table_name];
Operators are symbols or keywords that take one or more values (also called operands), performs a particular operation or function on these values, and return a single result.
Comparison operators
< – Less than. Example: 5 < 7, which returns TRUE.
> – Greater than. Example: 12 > 16, which returns FALSE.
<= – Less than or equal to. Example: 4 <= 2, which returns FALSE.
>= – Greater than or equal to. Example: 12 >= 12, which returns TRUE.
!= or <> – Not equal to (aka inequality test). Example: 'Slick' != 'Sl', which returns TRUE.
= – Equal to (aka equality test). Example: 34 = 35, which returns FALSE.
These operators return a boolean value, and the expressions formed from these values and symbols are called conditions.
Logical operators
NOT – Inverts the value of the condition. Example: NOT 5 < 7, which returns FALSE. To negate a compound expression, add the parentheses after NOT, and place the compound expression between the parentheses. Example: NOT (first_letter != 'S' AND id < 50)
AND – True if both conditions are true, otherwise false. Example: TRUE AND FALSE, which returns FALSE.
OR – True if any of the conditions is true, otherwise false. Example: FALSE OR FALSE OR TRUE, which returns TRUE.
Other conditional operators
LIKE – See Like Operator for more details.
BETWEEN – Returns TRUE if the value is within the corresponding range. Example: 4 BETWEEN 2 AND 9, which returns TRUE. It can also be used with strings (by comparing its first letter or with succeeding letters). Example: 'Frosty' BETWEEN 'G' AND 'R' is FALSE. Note that the comparison of the strings is case-sensitive.
IS NULL – Returns TRUE if the value is null, otherwise FALSE.
IS NOT NULL – Just like IS NULL but the opposite.
Arithmetic operators
+ – Addition
- – Subtraction
* – Multiplication
/ – Division
These operators ordinarily return a number. An expression formed from these is called an arithmetic expression.