SQL Fundamentals Series
- SQL Fundamentals Series, is a training course that is purposed to deliver the content for the basic knowledge of the framework and its functions that are important for every SQL user to know.
- SQL Fundamentals series acquaints you with a few basic SQL operations that facilitate the process of querying and interacting with databases.
- We plan to cover the following crucial topics in this SQL Fundamentals: SQL Wildcard Characters, SQL Comments, SQL Auto Increment, Cursors in SQL, and how to get the current date and time in SQL.
- Whether you’re a beginner or want to upgrade your knowledge, you can benefit from this SQL Fundamentals series to see what this happily grounds SQL knowledge and this in turn can lead to efficiency in database management means.Let’s start.
1. SQL Wildcard Characters
- SQL Wildcard characters are used in LIKE queries to help match patterns in text-based data.
- They allow you to search for a substring of a value rather than an exact match, so that it makes easier to find data that fits a given pattern.
- There are two main wildcard characters in SQL:
Percentage (%)
- The percentage symbol is a placeholder for any sequence of characters, and it includes the situation when nothing is matched.
- The character is used to match sections of a string.
- Example:
SELECT * FROM employees
WHERE name LIKE 'J%';
The query selected will be the ones in the employees table, and the ones whose name starts with the letter “J.”
Underscore (_)
- The underscore character represents one letter.
- It is the character used as a wild card when we want to refer to any character in a particular position of the string.
- Example:
SELECT * FROM employees
WHERE name LIKE '_ohn';
The above mentioned query will bring Jose, Jim or Jim. It does not depend on whether there is one or more letters before ohn.
Combination Example:
SELECT * FROM products
WHERE product_name LIKE 'A__%';
The query with above condition will find all products starts with “A” and followed by exactly two characters, like “Apple” or “Almond”.
2. SQL Comments
- SQL Comments give developers the ability to add notes to their queries, which help others (or even themselves) to grasp the code, thereby making the code more readable and maintainable.
- There are two comment types in SQL:
Single-line Comment
- Single-line comments are designated with two hyphens (–), and are terminated when a line concludes.
- Example:
-- This query allows to fetch the details of all IT department employees.
SELECT * FROM employees WHERE department = 'IT';
Multi-line Comment
- Multi-line comments are comments that are introduced with the constituent of /* at the beginning of the comment and then finished by the constituent of */ at the end.
- These are comments that can be written in more than one line.
- Example:
/*
This query retrieves all employees in the IT department
who are older than 30 years.
*/
SELECT * FROM employees
WHERE department = 'IT' AND age > 30;
SQL comments are necessary to document the code and make it understood by others; besides, it keeps track of the query purpose and logic.
3. SQL Auto Increment
- An SQL Auto Increment is a feature available in a structure of a database system designed to enable a unique number to be generated whenever a new record is inserted into a table.
- It is typically used to generate primary key values without the user having to manually input the unique values.
- In SQL, the AUTO_INCREMENT attribute is used with integer columns usually and it adds 1 to the value of the column for every new row.
How to Use Auto Increment:
- When creating a table with an auto-incrementing column, you just need to define the column as AUTO_INCREMENT in MySQL, or SERIAL in PostgreSQL.
- Example in MySQL:
CREATE TABLE employees (
employee_id INT AUTO_INCREMENT,
name VARCHAR(100),
department VARCHAR(50),
PRIMARY KEY (employee_id)
);
Inside the table, employee_id will be incremented automatically with new record creation, the starting default being 1.
Inserting Data:
INSERT INTO employees (name, department)
VALUES ('John Doe', 'IT');
This statement will exclusively and automatically make the identification of the new employee_id number of the new rows added to the table.
4. What is Cursor in SQL?
- A Cursor in SQL is a database object through which rows can be intercepted, manipulated, and navigated through the result set obtained from a query.
- Cursors are of major importance when there is a need for the same operations to be processed on each row of the result set or when the iterative approach is used for the result set.
- The usages of cursors are mainly found in stored procedures and triggers.
- The advantage they bring is that there is more control that can be exercised over the data retrieval process.
Types of Cursors:
- Implicit Cursor: It is formed spontaneously during SQL operations that deal with single queries that return a result set.
- Explicit Cursor: It is a user-defined handle that provides the user with limited control over the result set.
How to Use a Cursor:
- Declare the Cursor: As a first step, you will have to declare a cursor in the select statement.
- Open the Cursor: What comes next is that when the cursor is declared it will need to be opened to process the cursor.
- Fetch Data: Retrieve rows one by one as the SELECT statement proceeds.
- Close the Cursor: Once the statements are finished, close the cursor.
- Example:
DECLARE employee_cursor CURSOR FOR
SELECT employee_id, name FROM employees;
OPEN employee_cursor;
FETCH NEXT FROM employee_cursor INTO @employee_id, @name;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Process data here
FETCH NEXT FROM employee_cursor INTO @employee_id, @name;
END
CLOSE employee_cursor;
DEALLOCATE employee_cursor;
In this instance, a cursor is employed to iterate through the employees table by processing one row at a time.
5. How Does One Come to the Information regarding the Current Date and Time in SQL?
- Getting the current date and time in SQL is a common operation to evade or evaluate data; this is usually the most efficient way to go about it.
- It is important to note that different databases usually use different functions to retrieve the current date and time.
MySQL uses the NOW() function:
SELECT NOW();
This gives the current date and time.
PostgreSQL uses CURRENT_TIMESTAMP:
SELECT CURRENT_TIMESTAMP;
SQL Server uses GETDATE():
SELECT GETDATE();
Oracle uses SYSDATE:
SELECT SYSDATE FROM dual;
The functions fetch the current date and time as per the ticker of the server system.