PostgreSQL Syntax and Functions

0.0(0)
Studied by 1 person
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/30

flashcard set

Earn XP

Description and Tags

Flashcards on PostgreSQL syntax, usage, and functions for exam preparation.

31 Terms

1
New cards

What statement is used to retrieve data from a database in PostgreSQL?

The SELECT statement.

2
New cards

How can you specify which columns to select in a SQL query?

By specifying the column names in the SELECT statement.

3
New cards

What does the wildcard '*' do in a SELECT statement?

It selects all columns from the specified table.

4
New cards

What is the purpose of the SELECT DISTINCT statement?

To return only distinct (different) values from a column.

5
New cards

How can you count distinct values in a column using SQL?

By using the COUNT(DISTINCT column_name) function.

6
New cards

What is the function of the WHERE clause in a SQL query?

To filter records based on specified conditions.

7
New cards

What type of values requires quotes in PostgreSQL?

Text values.

8
New cards

How would you return all records where customer_id is greater than 80?

SELECT * FROM customers WHERE customer_id > 80;

9
New cards

What SQL keyword is used to sort results?

The ORDER BY keyword.

10
New cards

How do you sort records in descending order?

By using the DESC keyword after the ORDER BY clause.

11
New cards

What does the LIMIT clause do in SQL?

Limits the maximum number of records returned.

12
New cards

How does the OFFSET clause work in SQL?

Specifies where to start selecting records when using LIMIT.

13
New cards

What function does MIN() serve in SQL?

Returns the smallest value of the selected column.

14
New cards

What is the purpose of the MAX() function in SQL?

Returns the largest value of the selected column.

15
New cards

How can you rename a column returned by an aggregate function?

By using the AS keyword.

16
New cards

What does the COUNT() function return in SQL?

The number of rows that match a specified criterion.

17
New cards

How does the SUM() function work?

It returns the total sum of a numeric column.

18
New cards

What does the AVG() function calculate?

The average value of a numeric column.

19
New cards

How can you round an average to two decimal places in SQL?

By using the ::NUMERIC operator.

20
New cards

What operator is used to search for patterns in a column?

The LIKE operator.

21
New cards

What do the wildcards '%' and '_' represent in SQL?

'%' represents zero or more characters, and '_' represents a single character.

22
New cards

How would you return customers with a name that starts with 'A'?

SELECT * FROM customers WHERE customer_name LIKE 'A%';

23
New cards

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.

24
New cards

What is the difference between LIKE and ILIKE?

ILIKE is case-insensitive while LIKE is case-sensitive.

25
New cards

What does the IN operator do in SQL?

Allows you to specify a list of possible values in the WHERE clause.

26
New cards

How does NOT IN work in SQL?

Returns records that do not match any values in a specified list.

27
New cards

What does GROUP BY do in SQL?

Groups rows that have the same values into summary rows.

28
New cards

What aggregate functions are often used with GROUP BY?

COUNT(), MAX(), MIN(), SUM(), AVG().

29
New cards

How can you check the number of customers in each country?

SELECT COUNT(customer_id), country FROM customers GROUP BY country.

30
New cards

What keyword is used for giving a new name to a column in SQL?

The AS keyword.

31
New cards

What would be the SQL statement to select the total number of customers from the customers table?

SELECT COUNT(customer_id) FROM customers;