SQL Lecture Notes I
13 Questions
0 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to Lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What does SQL stand for?

Structured Query Language

Which of these clauses is required in a SELECT statement?

  • SELECT (correct)
  • WHERE
  • FROM (correct)
  • GROUP BY
  • ORDER BY
  • HAVING

SQL clauses are case-sensitive.

False (B)

What is the purpose of the * wildcard in a SELECT statement?

<p>The wildcard <code>*</code> selects all columns from the specified table(s).</p> Signup and view all the answers

Which clause is used to filter rows based on specific criteria?

<p>WHERE (A)</p> Signup and view all the answers

How do you specify a column in a SELECT statement when it has a unique name across multiple tables?

<p>You can use the column name directly without the table name (TableName.ColumnName).</p> Signup and view all the answers

What is the purpose of the ORDER BY clause?

<p>The ORDER BY clause determines the order in which the rows are sorted in the result set.</p> Signup and view all the answers

What is the difference between ASC and DESC in the ORDER BY clause?

<p>ASC specifies ascending order (smallest to largest), while DESC specifies descending order (largest to smallest).</p> Signup and view all the answers

The NOT operator can only be used with the WHERE clause.

<p>False (B)</p> Signup and view all the answers

What is the purpose of the SELECT DISTINCT clause?

<p>To remove duplicates (B)</p> Signup and view all the answers

What is an alias, and when is it useful in SQL?

<p>An alias assigns an alternative name to a table or column in SQL. It can help shorten long names or provide a more descriptive name.</p> Signup and view all the answers

Which character is commonly used as a wildcard in MySQL LIKE statements?

<p>% (B)</p> Signup and view all the answers

Aliases in SELECT only impact how the result is viewed and do not affect other parts of the query.

<p>True (A)</p> Signup and view all the answers

Flashcards

SQL

A standardized computer language for querying, altering, and defining relational databases.

SELECT statement

Used to query data in SQL, composed of clauses.

SELECT DISTINCT

Removes duplicate rows from the query result.

FROM clause

Specifies the table(s) to query from.

Signup and view all the flashcards

WHERE clause

Filters rows based on specified conditions.

Signup and view all the flashcards

GROUP BY clause

Groups rows with similar values into summary rows.

Signup and view all the flashcards

HAVING clause

Filters grouped results.

Signup and view all the flashcards

ORDER BY clause

Sorts the query results.

Signup and view all the flashcards

Wildcard (*)

Represents all columns in a table.

Signup and view all the flashcards

Calculated fields

Performing calculations on columns to create new columns.

Signup and view all the flashcards

AS clause

Gives a calculated field a name.

Signup and view all the flashcards

ASC order

Sorts in ascending order (alphabetical or smallest to largest).

Signup and view all the flashcards

DESC order

Sorts in descending order (reverse alphabetical or largest to smallest).

Signup and view all the flashcards

Logical conditions

Criteria used in WHERE clauses.

Signup and view all the flashcards

Table/column names with spaces

Enclose them in square brackets [] to prevent errors.

Signup and view all the flashcards

Aliases with spaces

Enclose in single quote marks to work properly.

Signup and view all the flashcards

SQL syntax

Combination of keywords and clauses with specific order.

Signup and view all the flashcards

Database

Organized collection of structured data.

Signup and view all the flashcards

LIKE wildcard (*, ?)

Used for pattern matching in WHERE clause.

Signup and view all the flashcards

Employee Table

Example table containing employee data.

Signup and view all the flashcards

Hire Date

Date when employee joined.

Signup and view all the flashcards

Salary

Employee's compensation.

Signup and view all the flashcards

Data types

Represent different kinds of information.

Signup and view all the flashcards

Study Notes

SQL Lecture Notes I

  • SQL is a standardized computer language used to query, alter, and define relational databases. It uses declarative statements.
  • SQL queries are constructed using a SELECT statement, followed by clauses in this order:
    • SELECT DISTINCT (optional)
    • FROM (required)
    • WHERE (optional)
    • GROUP BY (optional)
    • HAVING (optional)
    • ORDER BY (optional)
    • ; (semicolon to end the statement - required)
  • Example data table: Employee table with fields like EmployeeID, FirstName, LastName, Building, Department, HireDate, Salary, and JobRating.

Wildcards

  • SELECT * FROM Employee; — retrieves all columns and rows from the Employee table.
  • * is a wildcard that represents all columns.

Calculated Fields

  • Calculations can be performed within the SELECT statement. For example, Quantity * UnitPrice creates a new column of calculated values.
  • Dates can also be manipulated arithmetically in queries.
  • The AS clause allows assigning names to calculated fields. (e.g., AS new_column_name).

Sorting Rows (ORDER BY)

  • ORDER BY is used to sort data in a specific order.
  • ASC sorts data in ascending order (alphabetical, smallest to largest).
  • DESC sorts data in descending order (reversed alphabetical, largest to smallest).
  • Field sorting is left to right. (e.g., order by department first then by job rating next).

Filtering rows (WHERE)

  • WHERE is used to filter records based on specified criteria.
  • Common operators include:
    • = (equal)
    • <> or != (not equal)
    • >, <, >=, <= (greater than, less than, greater than or equal to, less than or equal to)
    • BETWEEN (between a specific range of values)
    • IN (is contained within a list of values)
    • IS NULL (checks for empty fields)
    • LIKE (pattern matching) - uses * (zero or more characters) or ? (single character)
  • Dates are often written using the # symbol in Ms Access.

Combining Logical Operators (AND, OR, NOT)

  • AND: Both conditions must be true.
  • OR: Either condition can be true.
  • NOT: Inverts the condition.

Aliases (SQL AS Clause)

  • Aliases provide short names for tables or columns in the FROM and SELECT clauses.
  • Aliases are especially helpful with long table or column names to improve readability and maintainability.

Removing Duplicates (SELECT DISTINCT)

  • SELECT DISTINCT used in a query will remove duplicate rows with the specified column, returning distinct values only.

Date Formats (Ms Access vs. MySQL)

  • Ms Access uses the format #MM/DD/YYYY# for dates.
  • MySQL uses single quotes 'YYYY-MM-DD' for dates.

Special Characters in Table/Column Names SQL

  • In some database systems, use square brackets ([]) when working with table/column names containing spaces or special characters in SQL queries.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Quiz Team

Related Documents

SQL Lecture Notes I PDF

Description

Explore the foundational concepts of SQL in this quiz. Learn how to construct queries using the SELECT statement and understand the use of wildcards and calculated fields. This quiz will help reinforce your knowledge of SQL syntax and its applications in relational databases.

More Like This

Use Quizgecko on...
Browser
Open
Browser
Browser