Fichas

Databases - SQL

SEZ McKelvey Inglés 22 fichas

Fichas del conjunto (22)

1SQL statements consist of 2 types of words, what are they? Is SQL case sensitive?-Reserved words (fixed part of SQL), User-defined words (made up by user, represent names ore relations, attributes, cell entries, etc)
Case insensitive except for literal character data (ie data entries)
2What are the 4 main statements of interest in DML?-Select (to query data)
-Insert (to insert new data into an existing table)
-Update (to update data in an existing table)
-Delete (to delete data from an existing table)
3What do the following represent in SQL: 1.Upper case letters 2.lowercase letters 3.vertical bar | 4.Curly braces {} 5.Square brackets [] 6.Ellipsis ... 7. *1. Reserved words
2. User-defined words
3. Choice among alternatives
4. Required element
5. Optional element
6. Optional repetition (0 or more)
7. All columns
4What is the syntax of a select-from sttatement?
5What does DISTINCT do?-In normalised relational tables there are no duplicated rows
-But the result of SELECT may have duplicate rows.
-Use distinct to eleminate duplicates.
6What are the search conditions that can follow WHERE?-Comparisons (compare values of two expressions)
-Range (BETWEEN/NOT BETWEEN)
-Set membership (IN/NOT IN)
-Pattern matching (LIKE/NOT LIKE)
7What do: LIKE 'H%' LIKE 'H_ _ _' LIKE '%e' NOT LIKE 'H%' do?1. first character must be H, rest can be anything
2. Exactly 4 characters, first must be H
3. Any sequence ending in e
4. First character cannot be H.
8how do you format ORDER BY?ORDER BY "column-list" [ASC|DESC], ["column-list2" [ASC|DESC]]....
9What are the aggregate functions and what do they do?
10What does GROUP BY do?
11List the properties that are handled by staff who work in the branch with the address '163 Main St'
12How do you use the ANY or SOME operator in multi value subqueries?
13How do you use the ALL operator in a multi-value subquery?
14What do joins do? What do you have to do in FROM clauses to use them?-Link data from two or more tables together in a single query
-Include more than one table in the FROM clause
-Separate these tables with a comma.
15How do you format a natural inner join? How do you use an Alias? What do natural inner joins do?
16Whwat do outer joins do and what are the types?
17What is the syntax of an INSERT command?
18What is the syntax of an UPDATE command?
19What is the syntax of a DELETE command? How do you delete a whole table?
20What is the syntax of a table creation? What are the options for DROP TABLE R?
21how do you add new attributes, remove attributes, and change attribute characteristics in the Alter Table construct?
22What are views and what are they used for?A relation that depends on other relations and is not physically stored as a table
-Used for presenting different information to different users, and simplifying complex queries.
CREATE VIEW viewname AS
SELECT...
-Can query just like tables
-But can run into updating issues if, eg, we don't have primary keys