SQL Commands Flashcards
Arrow keys or swipe to navigate cards
Master SQL commands with flashcards that simplify database management. Learn key concepts, essential queries, and advanced techniques for efficient data handling.
What is the purpose of the SELECT statement in SQL? The SELECT statement is used to retrieve data from one or more tables in a database. It allows specifying columns to display and filters using conditions.
What is the syntax for inserting data into a table using the INSERT statement?
The syntax for the INSERT statement is:
What does the UPDATE statement do in SQL? The UPDATE statement modifies existing records in a table. It can change one or more columns' values based on a condition.
What is the DELETE statement used for? The DELETE statement is used to remove rows from a table based on a specified condition. Without a condition, it deletes all rows.
What is the difference between DELETE and TRUNCATE? DELETE removes specific rows based on a condition and can be rolled back. TRUNCATE removes all rows from a table and is faster but cannot be rolled back.
What is the purpose of the CREATE statement in SQL?
The CREATE statement is used to create a new database object, such as a table, view, index, or database. For example:
What does the ALTER statement do in SQL?
The ALTER statement is used to modify an existing database object, such as adding, deleting, or modifying columns in a table. For example:
What is the syntax for the DROP statement in SQL?
The DROP statement is used to delete a database object such as a table or database. For example:
How is the CREATE DATABASE command used?
The CREATE DATABASE command is used to create a new database. The syntax is:
What is the difference between DROP and TRUNCATE? DROP deletes the table and its structure permanently, while TRUNCATE removes all rows from a table but keeps its structure intact for future use.
What is the purpose of the INSERT statement in SQL?
The INSERT statement adds new rows to a table. For example:
How can you insert data into a table by copying from another table?
Use the INSERT INTO ... SELECT statement. For example:
What does the UPDATE statement do in SQL?
The UPDATE statement modifies existing rows in a table. For example:
How can you update multiple columns in a single SQL query?
Use a comma-separated list in the SET clause. For example:
What is the purpose of the DELETE statement in SQL?
The DELETE statement removes rows from a table. For example:
How do you delete all rows from a table without removing its structure?
Use the DELETE statement without a WHERE clause. For example:
How can you return the rows affected by an UPDATE statement?
Use the RETURNING clause if supported by the database. For example:
How do you insert data with default values for some columns?
Specify only the desired columns in the INSERT statement. For example:
How can you delete rows based on a join condition?
Use a DELETE statement with a subquery or JOIN. For example:
How can you use the INSERT ON CONFLICT or REPLACE statement?
These are used to handle duplicates during an INSERT. For example (PostgreSQL):
What is the purpose of the WHERE clause in a SELECT query?
The WHERE clause is used to filter rows based on a specified condition. For example:
How do you use the GROUP BY clause in a query?
The GROUP BY clause groups rows with the same values in specified columns and allows aggregate functions to be applied. For example:
What is the difference between INNER JOIN and LEFT JOIN? INNER JOIN returns rows with matching values in both tables, while LEFT JOIN returns all rows from the left table and matching rows from the right table, with NULLs for non-matches.
How do you filter grouped data using the HAVING clause?
The HAVING clause filters groups after aggregation. For example:
What is the purpose of the FULL JOIN? FULL JOIN returns all rows when there is a match in either table, filling in NULLs where there is no match.
How can you use a subquery in the SELECT clause?
A subquery in the SELECT clause returns a calculated value for each row. For example:
How do you retrieve unique rows using SELECT?
Use the DISTINCT keyword. For example:
What is a CROSS JOIN, and when is it used?
A CROSS JOIN produces a Cartesian product of two tables, combining each row from the first table with every row from the second. For example:
How do you filter rows using multiple conditions?
Use logical operators like AND, OR, and NOT. For example:
How can you sort query results in SQL?
Use the ORDER BY clause. For example:
What is the purpose of the GRANT statement in SQL?
The GRANT statement is used to provide specific privileges to users or roles. For example:
What does the REVOKE statement do in SQL?
The REVOKE statement removes specific privileges previously granted to a user or role. For example:
How can you grant all privileges on a database to a user?
Use the GRANT ALL PRIVILEGES statement. For example:
How can you check the permissions granted to a user?
You can query the database's system tables or use a SHOW GRANTS statement. For example:
What is the difference between GRANT and REVOKE? GRANT is used to assign permissions to users or roles, while REVOKE removes previously assigned permissions.
What is the purpose of the COMMIT statement in SQL? The COMMIT statement saves all changes made during the current transaction to the database permanently.
What does the ROLLBACK statement do in SQL? The ROLLBACK statement undoes all changes made during the current transaction, reverting the database to its previous state.
What is a SAVEPOINT in SQL, and how is it used?
A SAVEPOINT marks a specific point within a transaction, allowing partial rollbacks to that point. For example:
How can you set a transaction to begin explicitly in SQL?
Use the BEGIN TRANSACTION statement to start a new transaction explicitly. For example:
What happens if a transaction is not committed or rolled back? If a transaction is left open, changes remain uncommitted and are not visible to other users. They can be lost if the session ends unexpectedly.
What does the COUNT() function do in SQL?
The COUNT() function returns the number of rows that match a specified condition. For example:
How is the SUM() function used in SQL?
The SUM() function calculates the total sum of a numeric column. For example:
What is the purpose of the AVG() function in SQL?
The AVG() function calculates the average value of a numeric column. For example:
How is the MAX() function used in SQL?
The MAX() function returns the largest value in a column. For example:
What does the MIN() function do in SQL?
The MIN() function returns the smallest value in a column. For example:
How do you concatenate strings in SQL?
Use the CONCAT() function to join strings. For example:
How can you extract the year from a date column in SQL?
Use the YEAR() function. For example:
What is the purpose of the NOW() function in SQL?
The NOW() function returns the current date and time of the database server. For example:
How can you find the length of a string in SQL?
Use the LENGTH() function. For example:
What does the ROUND() function do in SQL?
The ROUND() function rounds a numeric column to a specified number of decimal places. For example: