Flashcards
MSSQL
Flashcards do conjunto (52)
- 1is probably the most used SQL command; used for retrieving rows from the database and enables the selection of one or many rows or columns from one or many tables in the databaseSELECT Statement
- 2select * from CUSTOMER
- 3select CustomerId, LastName, FirstName from CUSTOMERthe CustomerId, LastName, FirstName from CUSTOMER (table) will appear
- 4simplest form for select statementsselect from
(use the symbol * for all columns) - 5True or False SQL is not case sensitive. SELECT is the same as selectTRUE
- 6The full syntax of the SELECT statement is complex, but the main clauses can be summarized as...SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ]
] select_list [ INTO new_table ]
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression ] [ HAVING
search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ] - 7A ____ query is automatically created for you which you can edit if you want to.Select
- 8If you want the data to appear in a specific order you need to use the _____ keyword“order by”
- 9select * from CUSTOMER order by LastNameall columns of the CUSTOMER table will appear alphabetically
- 10select * from CUSTOMER order by Address, LastName-
- 11select * from CUSTOMER order by LastName desc-
- 12True or False In a table, some of the columns may contain duplicate values. This is a problem, however, sometimes you will want to list only the different (distinct) values in a table.False
it is NOT a problem - 13The ____ keyword can be used to return only distinct (different) values.DISTINCT
- 14syntax of DISTINCTselect distinct from
- 15select distinct FirstName from CUSTOMER
- 16The ____ clause is used to extract only those records that fulfill a specified criterion.WHERE
- 17syntax of WHEREselect
from
where operator value - 18select * from CUSTOMER where CustomerNumber='1001'shows from the table CUSTOMER whose customernumber is 1001
- 19True or False SQL uses single quotes around text values, as shown in the example aboveTRUE
- 20With the WHERE clause, what are the 9 operators that can be used
- 21select * from CUSTOMER where AreaCode>30all columns of those area codes greater than 30
- 22The ____ operator is used to search for a specified pattern in a column.LIKE
- 23LIKE syntaxSELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern - 24select * from CUSTOMER where LastName like 'J%'all the columns of those lastnames starting with J
- 25select * from CUSTOMER where LastName like '%a%'all the columns of those lastnames having "a"
- 26select * from CUSTOMER where LastName not like '%a%'all the columns of those lastnames NOT having "a"
- 27The ___ operator allows you to specify multiple values in a WHERE clause.IN
- 28IN SyntaxSELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...) - 29The ___ operator selects a range of data between two values. The values can be numbers, text, or dates.BETWEEN
- 30BETWEEN syntaxSELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2 - 31___________ can substitute for one or more characters when searching for data in a database.SQL wildcards
- 32SQL wildcards must be used with the SQL LIKE operatorTRUE
- 334 wild cards and descriptions%
A substitute for zero or more characters
_
A substitute for exactly one character
[charlist]
Any single character in charlist
[^charlist] or [!charlist]
Any single character not in charlist - 34SELECT * FROM CUSTOMER WHERE LastName LIKE 'J_cks_n'--
- 35SELECT * FROM CUSTOMER WHERE CustomerNumber LIKE '[10]%'--
- 36The ___ operator displays a record if both the first condition and the second condition is true.AND
- 37The __ operator displays a record if either the first condition or the second condition is true.OR
- 38select * from CUSTOMER where LastName='Smith' and FirstName='John'--
- 39select * from CUSTOMER where LastName='Smith' or FirstName='John'--
- 40You can also combine AND and OR (use parenthesis to form complex expressions).--
- 41select * from CUSTOMER where LastName='Smith' and (FirstName='John' or FirstName='Smith')--
- 42The ___clause is used to specify the number of records to return.TOP
- 43The ___ clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.TOP
- 44TOP syntaxSELECT TOP number|percent column_name(s)
FROM table_name - 45select TOP 1 * from CUSTOMER-
- 46select TOP 60 percent * from CUSTOMER-
- 47You can give a table or a column another name by using an ___. This can be a good thing to do if you have very long or complex table names or column names.alias
- 48SQL Alias Syntax for TablesSELECT column_name(s)
FROM table_name
AS alias_name - 49SQL Alias Syntax for ColumnsSELECT column_name AS alias_name
FROM table_name - 50SQL ___ are used to query data from two or more tables, based on a relationship between certain columns in these tables.JOINS
- 51joins example
- 52Return rows when there is at least one match in both tablesJOIN