1/30
Flashcards on PostgreSQL syntax, usage, and functions for exam preparation.
What statement is used to retrieve data from a database in PostgreSQL?
The SELECT statement.
How can you specify which columns to select in a SQL query?
By specifying the column names in the SELECT statement.
What does the wildcard '*' do in a SELECT statement?
It selects all columns from the specified table.
What is the purpose of the SELECT DISTINCT statement?
To return only distinct (different) values from a column.
How can you count distinct values in a column using SQL?
By using the COUNT(DISTINCT column_name) function.
What is the function of the WHERE clause in a SQL query?
To filter records based on specified conditions.
What type of values requires quotes in PostgreSQL?
Text values.
How would you return all records where customer_id is greater than 80?
SELECT * FROM customers WHERE customer_id > 80;
What SQL keyword is used to sort results?
The ORDER BY keyword.
How do you sort records in descending order?
By using the DESC keyword after the ORDER BY clause.
What does the LIMIT clause do in SQL?
Limits the maximum number of records returned.
How does the OFFSET clause work in SQL?
Specifies where to start selecting records when using LIMIT.
What function does MIN() serve in SQL?
Returns the smallest value of the selected column.
What is the purpose of the MAX() function in SQL?
Returns the largest value of the selected column.
How can you rename a column returned by an aggregate function?
By using the AS keyword.
What does the COUNT() function return in SQL?
The number of rows that match a specified criterion.
How does the SUM() function work?
It returns the total sum of a numeric column.
What does the AVG() function calculate?
The average value of a numeric column.
How can you round an average to two decimal places in SQL?
By using the ::NUMERIC operator.
What operator is used to search for patterns in a column?
The LIKE operator.
What do the wildcards '%' and '_' represent in SQL?
'%' represents zero or more characters, and '_' represents a single character.
How would you return customers with a name that starts with 'A'?
SELECT * FROM customers WHERE customer_name LIKE 'A%';
How do you search for records containing a specific letter or phrase?
By using '%' both before and after the letter or phrase in the LIKE operator.
What is the difference between LIKE and ILIKE?
ILIKE is case-insensitive while LIKE is case-sensitive.
What does the IN operator do in SQL?
Allows you to specify a list of possible values in the WHERE clause.
How does NOT IN work in SQL?
Returns records that do not match any values in a specified list.
What does GROUP BY do in SQL?
Groups rows that have the same values into summary rows.
What aggregate functions are often used with GROUP BY?
COUNT(), MAX(), MIN(), SUM(), AVG().
How can you check the number of customers in each country?
SELECT COUNT(customer_id), country FROM customers GROUP BY country.
What keyword is used for giving a new name to a column in SQL?
The AS keyword.
What would be the SQL statement to select the total number of customers from the customers table?
SELECT COUNT(customer_id) FROM customers;