Understanding SQL (Structured Query Language) is crucial when taking Exam DP-900: Microsoft Azure Data Fundamentals, as it’s the standard language for managing and manipulating databases. As you prepare for the exam, familiarize yourself with the common SQL statements and how they’re used in practice.
1. SELECT Statement
The SELECT statement retrieves data from a database. It’s the most commonly used statement in SQL. The basic syntax is:
SELECT column1, column2, …
FROM table_name;
If you want to select all columns, use the asterisk (*) symbol:
SELECT * FROM table_name;
2. INSERT INTO Statement
The INSERT INTO statement adds new rows of data to a table. The basic syntax includes the table name, columns, and values:
INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
If you’re adding values for all columns, you don’t need to specify the column names:
INSERT INTO table_name
VALUES (value1, value2, …);
3. UPDATE Statement
The UPDATE statement modifies existing records in a table. It’s combined with the SET and WHERE clauses. The basic syntax is:
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
The WHERE clause specifies which records to update. Without it, all records get updated, which can lead to data loss.
4. DELETE Statement
The DELETE statement removes existing records from a table. It’s often used with the WHERE clause to specify which records to delete:
DELETE FROM table_name WHERE condition;
Without the WHERE clause, the DELETE statement removes all records from the table.
5. CREATE TABLE Statement
The CREATE TABLE statement creates a new table in the database. The syntax includes the table name and column details (names, data types, and size):
CREATE TABLE table_name (
column1 datatype(size),
column2 datatype(size),
column3 datatype(size),
….
);
6. ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete/drop, or modify columns in an existing table. Here is how you add a new column:
ALTER TABLE table_name
ADD column_name datatype(size);
And to delete a column:
ALTER TABLE table_name
DROP COLUMN column_name;
7. DROP TABLE Statement
The DROP TABLE statement removes an existing table from the database:
DROP TABLE table_name;
Be careful with this statement because once a table is dropped, all the information available in the table is lost.
By understanding and practicing these common structured query language statements, you are well on your way towards acing the DP-900 Microsoft Azure Data Fundamentals exam. Always remember, practical application is key when it comes to retaining and proficiently using any coding language. So, make sure you apply these SQL statements in real scenarios to deepen your understanding.
Practice Test
The SQL SELECT statement is used to retrieve data from the database.
- a) True
- b) False
Answer: a) True
Explanation: The SQL SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.
The SQL DELETE statement is used to insert new records in a database.
- a) True
- b) False
Answer: b) False
Explanation: The SQL DELETE statement is used to delete existing records in a database, not to insert new records.
Which among the following SQL statement is used to modify the existing records in a table?
- a) SELECT
- b) DELETE
- c) UPDATE
- d) CREATE
Answer: c) UPDATE
Explanation: The SQL UPDATE statement is used to modify the existing records in a table.
The SQL CREATE DATABASE statement is used to create a new SQL database.
- a) True
- b) False
Answer: a) True
Explanation: The CREATE DATABASE statement is used to create a new database in SQL.
The SQL DROP TABLE statement removes a table and all its data permanently from the database.
- a) True
- b) False
Answer: a) True
Explanation: The DROP TABLE statement is used to drop an existing table in a database. It permanently removes a table and all of its data.
What does the SQL keyword WHERE do?
- a) Indicates the table to select from
- b) Defines or alters a table
- c) Inserts data into a table
- d) Filters the results based on a condition
Answer: d) Filters the results based on a condition
Explanation: The WHERE keyword is used to filter records, and include only records that fulfill a specified condition.
What is the purpose of the ALTER statement in SQL?
- a) Remove a column in the table
- b) Rename a table
- c) Add a column in the table
- d) All of the above
Answer: d) All of the above
Explanation: The ALTER statement in SQL is used to add, delete/drop or modify columns in an existing table. It is also used to add and drop various constraints on an existing table.
The SQL INSERT INTO statement is used to insert new records in a database.
- a) True
- b) False
Answer: a) True
Explanation: The SQL INSERT INTO statement is used to insert new records in a database.
The assignment of a default value to a column can be set using which SQL statement?
- a) INSERT INTO
- b) ALTER TABLE
- c) SELECT
- d) UPDATE
Answer: b) ALTER TABLE
Explanation: Default values for the columns can be set with ALTER TABLE statement when the column is initially created in the table, or after the table is created.
The SQL TRUNCATE TABLE statement can be rolled back.
- a) True
- b) False
Answer: b) False
Explanation: The TRUNCATE TABLE statement is a data definition language (DDL) operation that is used to mark the extents of a table for deallocation (empty for reuse). The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms intended to protect data, and once done, it can’t be rolled back.
Is the following SQL statement correct: “DELETE * FROM table_name;”?
- a) True
- b) False
Answer: b) False
Explanation: The correct syntax of the DELETE command is “DELETE FROM table_name;” The asterisk (*) should not be used in the DELETE statement.
To rename a column name or column datatype, which SQL statement is used?
- a) ALTER TABLE
- b) CREATE TABLE
- c) SELECT
- d) DROP TABLE
Answer: a) ALTER TABLE
Explanation: ALTER TABLE statement in SQL is used to add, delete/drop or modify columns in an existing table. It can also be used to add and drop various constraints on an existing table.
The SQL SELECT DISTINCT statement is used to return only distinct (different) values.
- a) True
- b) False
Answer: a) True
Explanation: The SELECT DISTINCT statement is used to return only distinct (unique) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
Is the following SQL statement correct: “INSERT INTO table_name (column1, column2) VALUES (value1, value2);”?
- a) True
- b) False
Answer: a) True
Explanation: The syntax of the SQL INSERT INTO statement: “INSERT INTO table_name (column1, column2) VALUES (value1, value2);” is correct. It defines the table to insert into (table_name), the columns to fill (column1, column2), and respective values to insert (value1, value2).
In an SQL statement, “LIMIT 10” would limit the output to 10 results.
- a) True
- b) False
Answer: a) True
Explanation: The “LIMIT 10” clause in SQL is used to limit the data amount returned by the SELECT statement. So, it would limit the output to 10 results.
Interview Questions
1. What is the primary function of SQL SELECT statement?
The primary function of SQL SELECT statement is to retrieve data from a database table.
2. What is the purpose of SQL WHERE clause in a SELECT statement?
The SQL WHERE clause is used to filter records based on a specified condition in a SELECT statement.
3. How is data sorted in a SQL SELECT statement using ORDER BY clause?
Data is sorted in a SQL SELECT statement using the ORDER BY clause followed by the column name by which the data should be sorted.
4. What is the purpose of SQL INSERT INTO statement?
The SQL INSERT INTO statement is used to insert new records into a database table.
5. How can you update existing records in a database table using SQL?
Existing records in a database table can be updated using the SQL UPDATE statement with the SET keyword to specify the columns to be updated.
6. What is the use of SQL DELETE statement?
The SQL DELETE statement is used to remove existing records from a database table based on a specified condition.
7. How can you retrieve distinct records from a database table using SQL?
Distinct records can be retrieved from a database table in SQL using the DISTINCT keyword in a SELECT statement.
8. What is the purpose of SQL JOIN statement?
The SQL JOIN statement is used to combine rows from two or more tables based on a related column between them.
9. How can you group records together in SQL using GROUP BY clause?
Records can be grouped together in SQL using the GROUP BY clause followed by the column name by which the records should be grouped.
10. What is the purpose of SQL HAVING clause?
The SQL HAVING clause is used to filter records in a GROUP BY clause based on a specified condition.
11. How can you limit the number of records returned in a SQL SELECT statement?
The number of records returned in a SQL SELECT statement can be limited using the LIMIT keyword followed by the desired row count.
12. What is the use of SQL LIKE operator?
The SQL LIKE operator is used to search for a specified pattern in a column when retrieving records from a database table.
13. How can you perform calculations on data in a SQL query?
Calculations can be performed on data in a SQL query using arithmetic operators like +, -, *, /, etc., within the SELECT statement.
14. What is the purpose of SQL UNION operator?
The SQL UNION operator is used to combine the result sets of two or more SELECT statements into a single result set.
15. How can you create a new table in a database using SQL CREATE TABLE statement?
A new table can be created in a database using the SQL CREATE TABLE statement followed by the table name and column definitions.
extutorials.com