Mastering the SQL SELECT Statement for Efficient Data Retrieval

Note: All SQL queries used in the Case study is tested on Oracle,SQLServer,MySQL and PostgreSQL.

1.Write a query to retrieve the Name and Email of all customers from the Customers table.
SELECT Name, Email FROM Customers;
The basic SQL statement built to fetch information about the details of certain customers that exist within the Customers table is the select statement as SELECT Name, Email FROM Customers; It uses the SELECT keyword in its command, telling the database to retrieve the values existing in two columns Name and Email for every customer in the table. The FROM clause determines the table upon which the query has to select data for example, in this case the Customers table for customer information. Since there is no filter or conditions in this query, it will simply extract and print all of the customer names and email by showing the Name and Email columns for each customer in the list of the Customers table. The results appear as follows a table with two columns Name and Email with its corresponding information for each of the customers listed in the Customers table. This sort of question is usually used when you need retrieving basic contact information like names and email addresses without any requirement of filtering or sorting.

2.Write a query to fetch AccountID and Balance from the Accounts table.
SELECT AccountID, Balance FROM Accounts;

3.Write a query to retrieve all columns from the Transactions table.
SELECT * FROM Transactions;
The query is to retrieve all the columns in the Transactions table. Since the SELECT * statement implies that it is a request to select every column of the table, there is no specific column defined in this case. The '*' symbol is a wild-card to retrieve every column available in the Transactions table. The FROM Transactions clause defines the source table that is Transactions in this case. This table should have information about each transaction. For example, it would include TransactionID, AccountID, tDate the date of the transactions, Amount and TransactionType Such as Deposit, Withdrawal.
The query will return all the rows from the Transactions table if run because every row provides full transaction details for each of the records stored. This type of query is helpful when you want to view all your transaction data in its entirety without any filtering or restrictions applied. It finds most useful in reports or data extraction jobs where further analysis or review of all sides of a transaction is required for further processing or analysis.

4.Write a query to fetch the CustomerID, Name, and Email of customers.
SELECT CustomerID, Name, Email FROM Customers;
This SQL statement will select all records in the Customers table when the columns requested include CustomerID, Name and Email. Three columns are selected from the table without any filtering conditions on the rows that match the query because there is no WHERE clause. All records in the Customers table are returned this is a simple query to get the simple customer information such as their ID, name and email address usually used for and communicated with customer related processes.

5.Write a query to retrieve all columns from the Customers table.
SELECT * FROM Customers;
It would fetch everything from the table Customers using the statement SELECT * the asterisk symbol is considered a wildcard thus, it fetches all columns within the given table. In this case, it fetches all the data about customers located within the Customers table including CustomerID, Name, Email, Phone, Address and possibly other columns if they exist in the table. This is helpful when one has to retrieve an entire record of customers without having to mention each column individually.

Example of an SQL SELECT statement for retrieving data from a relational database

6.Write a query to retrieve all columns from the Transactions table where the transaction amount is greater than 500.
SELECT *
FROM Transactions
WHERE Amount > 500;
It returns the results of all columns in the Transactions table with a transaction Amount above 500. The SELECT * statement will indicate SQL to return all columns from the Transactions table. The WHERE clause is used for applying filters to the result, so only the records where Amount is more than 500 can be selected. This is helpful when you want to evaluate greater transactions like deposits or withdrawals of more than a certain amount from the Transactions.

7.Write a query to fetch all customer records without using *.
SELECT CustomerID, Name, Email, Phone, Address FROM Customers;
This statement retrieves all the records in the Customers table but this time it does not use the wildcard *. Rather, it explicitly lists out each column CustomerID, Name, Email, Phone and Address. Listing columns separately gives you much greater control over what the database returns and that's really useful if you only want a few specific columns returned in your result set. It can also help performance especially where there are many columns on a table but one only needs a few.

8.Write a query to retrieve all transaction details for a specific AccountID (e.g., 101).
SELECT * FROM Transactions WHERE AccountID = 101;
It will bring up all information from the Transactions table about the transactions where the AccountID value is 101. The * is used to get all columns for the relevant records of that table. The WHERE clause narrows the output only to include the transactions where the AccountID is 101. This would be helpful in isolating and referencing the account's transaction history and therefore, revealing every transaction going over that account like deposits and withdrawals.

9.Write a query to find customers who are active (assuming IsActive = 1 exists in the Customers table).
SELECT * FROM Customers WHERE IsActive = 1;
This query retrieves all the records of customers in the Customers table whose IsActive column has a value of 1. The column IsActive is used to indicate whether a customer is active or not (1 indicating active and 0 indicating inactive). Using the WHERE clause limits the records it returns to just the customers who are active that is, whose IsActive status is equal to 1. This query will assist in identifying and managing the active customers within the system.

10.Write a query to retrieve the details of customers whose Name starts with 'J'.
SELECT * FROM Customers WHERE Name LIKE 'J%';
This statement will return every record in the Customers table where the Name begins with the letter 'J'. The LIKE operator is used to for pattern matching and the '%' symbol is a wildcard that can represent any sequence of characters. So 'J%' matches any name beginning in 'J' and containing any characters after 'J'. This would be useful in filtering and finding customers with names beginning on a specific letter or pattern an example here being 'J'.

11.Write a query to fetch customers who have an email address that contains "gmail.com".
SELECT * FROM Customers WHERE Email LIKE '%gmail.com';
This query fetches all rows in the Customers table whose Email column contains the domain "gmail.com". The LIKE operator is a pattern matching operator. Percent (%) is a wildcard meaning match any possible sequence of characters preceding "gmail.com". This means that fetch will happen for customers whose email address is ended by "@gmail.com" irrespective of what precedes the domain. For example, john.doe@gmail.com, jane.smith@gmail.com. This kind of query is useful when you are filtering on emails from customers with a specific domain.

12.Write a query to list all transactions for the month of January 2024 (assuming tDate column exists in the Transactions table).
SELECT *
FROM Transactions
WHERE tDate BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD')
AND TO_DATE('2024-01-31', 'YYYY-MM-DD');
The question is to include all the transactions from the Transactions table that have been done in January 2024. With Oracle, one needs to specify how one treats dates in other words, in Oracle you need to use the TO_DATE() function when you want to convert a date string to the DATE datatype. So for example, it converts the string in the format 'YYYY-MM-DD' to a DATE Oracle value and likewise does the same for 'YYYY-MM-DD' as the date for the string to convert to a DATE. Using the BETWEEN operator, the query looks at the transactions only for those cases wherein the tDate or the transaction's date falls between two dates from the start to the end of January 2024. The *picks up all columns in the Transactions table for each record wherein the comparison had resulted in a match. This ensures proper date comparison especially in the case within the Oracle system that used to have a DATE type with both date and time in its information. The output is a list of all transactions within the given date range.

13.Write a query to find all accounts with a balance less than 500.
SELECT * FROM Accounts WHERE Balance < 500;
This fetches all Accounts records having balance less than 500. The WHERE clause is used to filter rows against the Balance column. Balance < 500 returns only accounts whose balance is less than 500. Such a query can be handy to identify low balance accounts and may be used in monitoring, reporting or further action against accounts that are not sufficient for the funds.

14.Write a query to retrieve customers whose Phone number contains '123'.
SELECT * FROM Customers WHERE Phone LIKE '%123%';
Retrieves all records from the table Customers for customers whose Phone number contains the string '123'. LIKE SQL operator is the pattern match operator to find a string of a specified pattern within a column. % symbols are wild cards. They can replace any length of characters (including zero.). Therefore %123% means Phone number must have digits '123' wherever it appears in the number along with the other characters preceding and following it. The asterisk in the SELECT clause will return all columns for those customers where this condition is met. This question will help in picking a customer whose telephone number consists of '123'. It would be useful to trace or confirm certain types of phone numbers, say a region or service provider.

15.Write a query to find all customers who have an address starting with 'Pune'.
SELECT * FROM Customers WHERE Address LIKE 'Pune%';
This query fetches all customers where the column Address starts with the string 'Pune'. The LIKE operator is used for pattern matching in SQL. The % symbol is used as a wildcard to represent any sequence of characters. 'Pune%' would mean that the Address has to start with 'Pune' and may be followed by any characters or none of them. This is very useful if you are ever likely to filter by city, region or any other condition based on an address. Customers whose address starts with 'Pune' have all columns returned this might be useful for analyzing customers in a particular area or region or city.

16.Write a query to list all transactions for a specific TransactionType (e.g., 'Deposit').
SELECT * FROM Transactions
WHERE TransactionType = 'Deposit';
This query returns all transaction records from the Transactions table where the TransactionType column is 'Deposit'. The WHERE clause with its condition will filter for rows in which the type of transaction happened to be a 'Deposit'. This can help in plenty of scenarios such as the evaluation of only deposits for a financial report or checking up on incoming funds for a specific time. The columns of every transaction that satisfies the condition will be returned to give an overview of every type of deposit transaction.

17.Write a query that will return all transactions whose TransactionType is 'Deposit' or 'Withdrawal'.
SELECT * FROM Transactions
WHERE TransactionType IN ('Deposit', 'Withdrawal');
This gives the record for all transactions in the table Transactions whose TransactionType falls under one of these selected types which is either 'Deposit' or 'Withdrawal'. The IN operator is being used to determine if the TransactionType has any of the specified values ('Deposit' or 'Withdrawal'). This is an efficient way to filter rows that meet one of multiple conditions. Because of IN, the query can return deposit as well as withdrawal transactions, which is useful for reporting on all kinds of transactions involving money movement into or out of accounts.

18.Write a query that will list customers whose account type is either 'Savings' or 'Checking'.
SELECT * FROM Customers
WHERE CustomerID
IN
(SELECT CustomerID FROM Accounts WHERE AccountType IN ('Savings, 'Checking'));
This query gets all of the records about customers in the Customers table where their CustomerID matches either a 'Savings' or 'Checking' account. The inner query gets the CustomerID values from the Accounts table where the value for AccountType is either 'Savings' or 'Checking'. The outer query then filters customers using the IN operator where their CustomerID matches any of the values obtained by the subquery. This approach makes possible the identification of customers who have specific types of accounts which may be relevant for targeted analysis or financial products.

19.Write a query to fetch all those transactions in which AccountID is either 102 or 104 or 106.
SELECT * FROM Transactions
WHERE AccountID IN (102, 104, 106);
This query returns all the records from the Transactions table where the AccountID is any one of the values: 102, 104 or 106. The IN operator will check if the AccountID matches any of these three values: 102, 104 or 106. This enables account specific filtering of transactions. By the use of IN, the query can retrieve multiple records that match any of the provided account IDs, which can be useful to find transactions specific to those accounts.

20.Write a SQL query to retrieve all accounts whose Balance is in the range of 1000 to 5000.
SELECT * FROM Accounts
WHERE BALANCE BETWEEN 1000 AND 5000;
Retrieve all records from the Accounts table where the Balance is between 1000 and 5000, inclusive. Through the use of the BETWEEN operator, range filtering allows easier fetching of accounts with balances in a given range. Such a query can be really useful when looking for accounts in a specific range of balances. for example, to examine customers having mid-range account balances. The query ensures that both boundary values 1000 and 5000 form part of the result set.

21.Find all customers who do not provide a phone number, such as Phone is NULL.
SELECT * FROM Customers
WHERE PHONE IS NULL;
This query fetches all the customer records from the Customers table where the Phone column is NULL. In database, NULL actually means that a value does not exist. Using the IS NULL condition on a column filters out all the customers who haven't provided a phone number for their corresponding contacts, whose follow-up can then be done subsequently or data can be cleaned up.

22.Write a query to return all transactions where the Amount is NULL, if possible in your database.
SELECT * FROM Transactions
WHERE Amount IS NULL;
This query fetches all the transaction records from the Transactions table in which Amount is NULL. NULL values for Amount could be because some transaction information was not provided or could not be retrieved within the amount, like failed transactions or transactions that have not yet entered the database. The IS NULL condition makes it fetch only those transactions with no mentioned amount. This might help identify data anomalies or inadequate records for some transactions in a table.

23.Write a query to find customers who have not provided their address (i.e., Address is NULL ).
SELECT * FROM Customers
WHERE address IS NULL;
This brings back all the records of customers in the Customers table when the Address column is NULL. This includes customers who have no records of their address. This is because no address information was given. This query is very useful for identifying customers who perhaps may not have completed their profiles or identifying records that require updates with missing address details.

24.Write query to retrieve balance of accounts where AccountID is NULL.
SELECT BALANCE FROM ACCOUNTS
WHERE AccountID IS NULL;
this query returns the balance values for which AccountID is NULL in the Accounts table. Usually, one would not find AccountID=NULL because AccountID is a primary key that is used to identify any account. So the ability of this query comes handy when such issues arise due to improper data or unexpected database conditions. This type of query will be able to return accounts with missing or invalid identifiers.

25.Write a query to get all inactive customers (IsActive is NULL or 0).
Note: In this example, the column IsActive is assumed to be in the Customers table. If not there, you can skip or eliminate this query.

SELECT ALL FROM Customers WHERE IsActive IS NULL OR IsActive = 0;
This question is supposed to return all customer records from the Customers table where IsActive is either NULL or equals 0. Typically, IsActive is a column of a table used to indicate if a customer is presently active. NULL might point to an unknown or unspecified status, but 0 would be indicative of inactivity. The use of IS NULL returns customers whose activity status has not been defined, whereas the IsActive = 0 condition identifies them to be explicitly marked inactive. Filtering off a status of inactive or undetermined is going to be pretty useful to clean up customer records.

26. Write a SQL Query that will return all customers where Email is not NULL.
SELECT * FROM Customers
WHERE EMAIL IS NOT NULL;
This query fetches all rows from the Customers table where the Email column is not NULL. This means customers will be filtered out for whose email addresses are missing or not recorded. This can be used when you need to ensure that all selected customers must have a valid email address in the customer's table, which is necessary for communication or marketing purposes.purposes.

27.Write a query that retrieves AccountID, CustomerID and Balance from the Accounts table using a CASE statement to display 'Low Balance' if balance is less than 1000 or 'High Balance' otherwise.
SELECT AccountID, CustomerID, Balance,
CASE WHEN Balance < 1000 THEN 'Low Balance'
ELSE 'High Balance' END AS BalanceStatus FROM Accounts;
This SQL query is for retrieving details from the Accounts table and classification of account balances using conditional logic. The query selects three columns: AccountID, CustomerID, and Balance, it adds a new derived column known as BalanceStatus using a CASE statement. The CASE statement tests the value appearing in the Balance column for each account. If the balance is below 1000, the label it assigns is 'Low Balance' otherwise, it assigns 'High Balance.' The described classification will allow an easier description of accounts with balances that require additional attention and services. The result of the query above returns the unique identifiers of accounts (AccountID), the corresponding customer (CustomerID) the existing balance and its status as 'Low Balance' or 'High Balance'. This is a useful question in business finance because the enterprise would be interested in segmenting accounts depending on their balance thresholds for better management and decision making. The direct application of the CASE statement in the query simplifies categorizing data, which would otherwise require quite complex logic outside the database. It helps ensure clear data retrieval with efficiency, particularly when trying to analyze financial trends or reports based on account balances.

28.Write a query that displays the TransactionType along with a label 'High Transaction' if Amount is greater than 1000, and 'Low Transaction' if it's less than or equal to 1000.
SELECT TransactionType,
CASE WHEN Amount > 1000 THEN 'High Transaction'
ELSE 'Low Transaction' END AS TransactionStatus
FROM Transactions WHERE Amount IS NOT NULL;
This query brings up the data from the Transactions table with a specific focus on the TransactionType and categorizing each type of transaction on its Amount. It makes use of a CASE statement to assign either 'High Transaction' or 'Low Transaction' as a label to each row. When the Amount of a specific transaction is more than 1000, then it is assigned as 'High Transaction', otherwise, it is categorized as 'Low Transaction'. The WHERE clause enforces that only rows where Amount is not NULL, thereby excluding the incompleteness or missing data in the results. This method allows for proper segregation of each transaction based on the value of its Amount, enabling the easy distinction of highly valued transactions from those of low value in the analysis of massive datasets. The query also simplifies by avoiding the use of JOIN or subqueries, that is, it works directly with the Transactions table. This makes the query efficient and at the same time provides very important insights into the information involved based on the transaction amount. The CASE statement is an ideal method of applying conditional logic to classify and label data in a single query.

Previous Topic:-->>Tables for Account Management || Next topic:-->>Restricting and Sorting Data in SQL