GoConqr has a new look! Let us know what you think.

Sign up free

Flashcards

SIT772 - SQL

Amber Saxon SIT772 English 200 cards

SIT772 - SQL

Cards in this deck (200)

  1. 1SQLStructure Query Language
  2. 2DDLData Definition Language
    Commands to create database objects such as; tables, indexes and views, as well as commands to define access rights.
  3. 3DMLData Manipulation Language
    Commands to Insert, Update, Delete and Retrieve Data
  4. 4DDL Commands examplesCREATE SCHEMA AUTHORIZATION, CREATE TABLE, NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, DEFAULT, CHECK, CREATE INDEX, CREATE VIEW, ALTER TABLE, CREATE TABLE AS, DROP TABLE, DROP INDEX, DROP VIEW
  5. 5DML Commands ExamplesINSERT, SELECT, WHERE, GROUP BY, HAVING, ORDER BY, UPDATE, DELETE, COMMIT, ROLLBACK,
  6. 6DML Comparison Operators examples=, <,>,<=,>=,<>, !=
  7. 7DML Logical Operators examplesAND/OR/NOT
  8. 8DML Special Operators examplesBETWEEN, IS NULL, LIKE, IN, EXISTS, DISTINCT
  9. 9DML Aggregate Functions examplesCOUNT, MIN, MAX, SUM, AVG
  10. 10CREATE SCHEMA AUTHORIZATIONCreates a database schema
  11. 11CREATE TABLECreates a new table in the user's database schema
  12. 12NOT NULLEnsures that a column will not have null values
  13. 13UNIQUEEnsures that a column will not have duplicate values
  14. 14PRIMARY KEYDefines a Primary Key for a Table
  15. 15FOREIGN KEYDefines a Foreign Key for a Table
  16. 16DEFAULTDefines a Default value for a Column (when no value is given)
  17. 17CHECKValidates data in an attribute
  18. 18CREATE INDEXCreates and index for a table
  19. 19CREATE VIEWCreates a dynamic subset of rows and columns from one or more tables (see Chapter 8, Advanced SQL)
  20. 20ALTER TABLEModifies a table's definition (adds, modifies, or deletes attributes or constraints)
  21. 21CREATE TABLE ASCreates a new table based on a query in the user's database schema
  22. 22DROP TABLEPermanently deletes a table (and its data)
  23. 23DROP INDEXPermanently deletes an index
  24. 24DROP VIEWPermanently deletes a View
  25. 25INSERTInsert rows into a table
  26. 26SELECTSelects attributes from rows in one or more tables or views
  27. 27WHERERestricts the selection of rows based on a conditional expression
  28. 28GROUP BYGroups the selected rows based on one or more attributes
  29. 29HAVINGRestricts the selection of grouped rows based on a condition
  30. 30ORDER BYOrders the selected rows based on one or more attributes
  31. 31UPDATEModifies an attribute's values in one or more table's rows
  32. 32DELETEDeletes one or more rows from a table
  33. 33COMMITPermanently saves data changes
  34. 34ROLLBACKRestores data to its original values
  35. 35=, <, >, <=, >=, <>, !=Used in conditional expressions
  36. 36AND/OR/NOTUsed in conditional expressions
  37. 37BETWEENChecks whether an attribute value is within a range
  38. 38IS NULLChecks whether a attribute value is NULL
  39. 39LIKEChecks whether an attribute value matches a given string pattern
  40. 40INChecks whether an attribute value matches any value within a value list
  41. 41EXISTSChecks whether a subquery returns any rows
  42. 42DISTINCTLimits values to unique values
  43. 43Aggregate FunctionsUsed with SELECT to return mathematical summaries on columns
  44. 44COUNTReturns the number of rows with non-null values for a given column
  45. 45MINReturns the minimum attribute value found in a given column
  46. 46MAXReturns the maximum attribute value found in a given column
  47. 47SUMReturns the sum of all values for a given column
  48. 48AVGReturns the average of all values for a given column
  49. 49AuthenticationThe process through which a DBMS verifies that only registered users can access the database
  50. 50SchemaA logical grouping of database objects, such as tables, indexes, views and queries, that are related to each other. Usually, a schema belongs to a single user or application
  51. 51FKForeign Key
  52. 52PKPrimary Key
  53. 53CHARFixed-length character data, 1 to 255 characters
  54. 54VARCHARVariable-length character data, 1 to 2,000 charcters.
    VARCHAR is automatically converted to VARCHAR2 in Oracle.
  55. 55NUMBERNumeric data. NUMBER (9,2) is used to specify numbers that have two decimal places and are up to nine digits long, including the decimal places.
    Some RDBMSs permit the use of a MONEY or a CURRENCY data type.
  56. 56NUMERICNumeric data. DBMSs that do not support the NUMBER data type typically use NUMERIC instead
  57. 57INTInteger values only. INT is automatically converted to NUMBER in Oracle
  58. 58SMALLINTSmall integer values only. SMALLINT is automatically converted to NUMBER in Oracle
  59. 59DATEDATE Formats vary. Commonly accepted formats are DD-MON-YYYY, DD-MON-YY, MM/DD/YYYY and MM/DD/YY
  60. 60NUMBER (L,D) or NUMERIC (L,D)The declaration NUMBER(7,2) or NUMERIC(7,2) indicates that numbers will be stored with two decimal places and may be up to seven digits long, including the sign and the decimal place (eg; 12.32 or -134.99)
  61. 61INTEGERMay be abbreviated as INT. Integers are (whole) counting number, so they cannot be used if you want to store numbers that require decimal places
  62. 62SMALLINTLike INTEGER but limited to integer values up to six digits. If your integer values are relatively small, use SMALLINT instead of INT.
  63. 63DECIMAL(L,D)Like NUMBER(L,D), but the storage length is a minimum specification. That is, greater lengths are acceptable, but smaller ones are not. DECIMAL(9,2), DECIMAL(9), and DECIMAL are acceptable
  64. 64CHAR(L)Fixed-length character for up to 255 characters. If you store strings that are not as long as the CHAR parameter value, the remaining spaces are left unused. Therefore, if you specify CHAR(25), strings such as Smith and Katzenjammer are each stored as 25 characters. However,a US area code is always three digits long, so CHAR(3) would be appropriate if you wanted to store such codes
  65. 65VARCHAR(L) or VARCHAR2(L)Variable-length character data. The designation VARCHAR2(25) or VARCHAR(25) will let you store charcters up to 25 characters long. However, unlike CHAR, VARCHAR will not leave unused spaces. Oracle automatically converts VARCHAR to VARCHAR2
  66. 66DATEStores dates in the Julian format.
  67. 67Other Data Types in SQLTIME, TIMESTAMP, REAL, DOUBLE, FLOAT, INTERVAL DAY TO HOUR, LOGICAL, CURRENCY, AUTONUMBER
  68. 68CREATE TABLEA SQL command that creates a table's structures using the characteristics and attributes given
  69. 69Reserved WordsWords used by a system that cannot be used for any other purpose.
    For example, in Oracle SQL, the word INITIAL cannot be used to name tables or columns
  70. 70Constraints examplesNOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK
  71. 71INSERT INTO publisher VALUES (21225,'Plain Donkey publisher','Smithson','615','223-3234','VIC','Y');- The row contents are entered between parentheses. Note that the first character after VALUES is a parenthesis and that the last character in the command sequence is also a parenthesis.
    - Character (string) and date values must be entered between single quotes (‘ ‘).
    - Numerical entries are not enclosed in single quotes.
    - Attribute entries are separated by commas.
    - A value is required for each column in the table.
  72. 72SELECT columnlist FROM tablelist [WHERE conditionlist ]Use the WHERE clause to add conditional restrictions to the SELECT statement that limit the rows returned by the query. The following syntax enables you to specify which rows to select:
  73. 73=Equal to
  74. 74<Less than
  75. 75<=Less than or equal to
  76. 76>Greater than
  77. 77>=Greater than or Equal to
  78. 78<> or !=Not Equal to
  79. 79+ -Addition
    Subtraction
  80. 80* / ^Multiple
    Divide
    Raise to the Power
  81. 81Logical Operators AND, OR, and NOT.OR: For the row to be selected at least one of the conditions must be true
    AND: For a row to be selected all the specified conditions must be true
    NOT: For a row to be selected the specified condition must be false.
  82. 82<> []< > means you have to provide the information to this item [ ] means the item is optional.
  83. 83What is the SQL command you use to add a row into the database?INSERT
  84. 84When can the NULL entry be accepted during adding a new row into a database?Only if the corresponding attribute is optional
  85. 85The result of a SQL SELECT statement is a ____ .Table
  86. 86When retrieving data using a SELECT statement, you can use logical operators in the ____ clause.WHERE
  87. 87Which of the following is correct? a) SQL indexes can be created on the basis of any selected attribute b) SQL indexes can be created on the basis of any unique attribute c) SQL indexes can be created on the basis of any NULL attribute d) SQL indexes can be created on the basis of any numeric attributeSQL indexes can be created on the basis of any selected attribute
  88. 88Changes made to table contents are not physically saved on disk until what occurs? a) Database is closed b) Program is closed c) COMMIT command is used d) Any of the aboved) any of the above
  89. 89A primary key should be defined as _____. a) NULL b) Not NULL c) Either of the above can be used d) none of the above are correctb) Not NULL
  90. 90Considering binary relationships, possible cardinality ratios are_____ . a) one:one b) 1:N c) M:N d) all of aboved) all of above
  91. 91Association among several entities is called a _____. a) Extraction b) Relationship c) Combination d) Associationb) Relationship
  92. 92In a relational database, all data are ____ via relations. a) normalised b) stored c) accessed d) stored and accessedd) stored and accessed
  93. 93Relationship participation_______ a) can be 1:1, 1:M or M:N b) may be unary, binary or ternary c) is either optional or mandatory d) may be recursivec) is either optional or mandatory
  94. 94A foreign key is ____ a) an attribute that is underlined b) an attribute that uniquely identifies each row in a table c) an attribute that depends on another attribute d) an attribute that relates to the primary key in another tabled) an attribute that relates to the primary key in another table
  95. 95What best describes the RENAME command? a) The RENAME command is not a valid command b) The RENAME command is used to rename a database c) The RENAME command is used to rename an SQL file d) The RENAME command is used to change the name of the database object, such as tables and viewsd) The RENAME command is used to change the name of the database object, such as tables and views
  96. 96To modify a database column, we need to use a what command? a) ALTER b) MODIFY c) CHANGE d) You can not modify a database column once it is createda) ALTER
  97. 97Which is not a valid SQL command? a) DROP b) WHERE c) ALTER d) SPECIFYd) SPECIFY
  98. 98What is a substitution variable used for in SQL? a) Substitution variables are used to store columns b) Substitution variables are used to store rows c) Substitution variables are used to store elements of command text d) Substitution variables are used to store tablesc) Substitution variables are used to store elements of command text
  99. 99Which is the correct statement? a) The JOIN operation selects all records that match either left or right table records b) The JOIN operation selects records that have matching values in both tables c) The JOIN operation selects records from the first table with matching right table records d) The JOIN operation selects records from the the second table with matching left table recordsb) The JOIN operation selects records that have matching values in both tables
  100. 100Which is the correct statement? a) The EXCEPT (MINUS) statement in SQL combines rows from two queries and returns only the rows that appear in the second set but not in the first b) The EXCEPT (MINUS) statement in SQL combines rows from two queries and returns only the rows that appear in both queries c) The EXCEPT (MINUS) statement in SQL combines rows from two queries and returns only the rows that appear in the first set but not in the second d) The EXCEPT (MINUS) statement in SQL combines rows from two queries and returns only the columns that appear in the first set but not in the secondc) The EXCEPT (MINUS) statement in SQL combines rows from two queries and returns only the rows that appear in the first set but not in the second
  101. 101The “property of closure” for relational algebra operators means a) The use of relational algebra operators on existing relations produces new relations b) The use of relational algebra operators on existing relations produces closed relations c) The use of relational algebra operators on existing relations produces existing relations d) The use of relational algebra operators on existing relations produces old relationsa) The use of relational algebra operators on existing relations produces new relations
  102. 102The SQL keyword BETWEEN is used: a) to limit the columns displayed b) for ranges c) to match any value within a value list d) None of the aboveb) for ranges
  103. 103Which one of the following sorts rows in SQL? a) SORT BY b) ALIGN BY c) ORDER BY d) GROUP BYc) ORDER BY
  104. 104To remove duplicate rows from the results of an SQL SELECT statement, the ____ qualifier specified must be included. a) ONLY b) UNIQUE c) SINGLE d) DISTINCTd) DISTINCT
  105. 105CREATE TABLECREATE TABLE works
    (employeename CHAR(15) NOT NULL,
    companyname CHAR(15) NOT NULL,
    salary NUMBER(7),
    PRIMARY KEY (employeename, companyname));
  106. 106INSERT INTOINSERT INTO works VALUES ('Jones', 'Values', 15000);
  107. 107Tables owned by userSELECT table_name FROM user_tables;
  108. 108To check if a certain table exists in your databaseSELECT table_name FROM user_tables
    WHERE table_name = 'works';
  109. 109DESCRIBE (view table structure)DESC works
  110. 110SELECT ALL (view data in atble)SELECT * FROM works;
  111. 111EXIT or QUIT Oracle SQLEXIT
    or
    QUIT
  112. 112PSEUDOCOLUMNSPSEUDOCOLUMNS
  113. 113DESCRIBEDESCRIBE
  114. 114DUAL TABLEDUAL TABLE
  115. 115RE RUN (slash symbol / )RE RUN (slash symbol / )
  116. 116DISTINCT (select distinct data... no data repetitions)DISTINCT (select distinct data... no data repetitions)
  117. 117Renaming Columns with ASRenaming Columns with AS
  118. 118Renaming columns with "AS" if there are spaces within the new title, double quotation marks must be used. eg: SELECT dept_id as "Department Identification"Renaming columns with "AS" if there are spaces within the new title, double quotation marks must be used. eg: SELECT dept_id as "Departme...
  119. 119ARITHMETIC ON RETURNED RESULTSARITHMETIC ON RETURNED RESULTS
  120. 120RENAMING AND CONCATENATING ||RENAMING AND CONCATENATING ||
  121. 121NVL Handling NULL valuesNVL Handling NULL values
  122. 122WHEREWHERE
  123. 123WHERE with OPERATOR >WHERE with OPERATOR &gt;
  124. 124COMPARISON OPERATORS in the WHERE clauseCOMPARISON OPERATORS in the WHERE clause
  125. 125ANY operatorANY operator
  126. 126SQL OPERATORSSQL OPERATORS
  127. 127IS NANIs Not A Number
  128. 128LIKE operator %LIKE operator %
  129. 129NOT LIKENOT LIKE
  130. 130BETWEEN between 15000 and 20000 (would return 15000 AND 20000 and everything in between)BETWEEN between 15000 and 20000 (would return 15000 AND 20000 and everything in between)
  131. 131NOT BETWEENNOT BETWEEN
  132. 132AND combining conditionsAND combining conditions
  133. 133OR combining conditionsOR combining conditions
  134. 134OPERATOR PRECEDENCEOPERATOR PRECEDENCE
  135. 135In which three situations does a transaction complete? a) When a DELETE statement is executed. b) When a ROLLBACK command is executed. c) When a PL/SQL anonymous block is executed. d) When a data definition language (DDL) statement is executed. e) When a TRUNCATE statement is executed after the pending transaction.b) When a ROLLBACK command is executed.
    d) When a data definition language (DDL) statement is executed.
    e) When a TRUNCATE statement is executed after the pending transaction.
  136. 136Which statement is true regarding the default behaviour of the order by clause? a) In a character sort, the values are case-sensitive. b) NULL values are not considered at all by the sort operation. c) Only those columns that are specified in the SELECT list can be used in the ORDER BY Clause. d) Numeric values are displayed from the maximum to the minimum value if they have decimal positions.a) In a character sort, the values are case-sensitive.
  137. 137Which 3 tasks can be performed using SQL functions built into Oracle Database? a) Displaying a date in a nondefault format. b) Finding the number of characters in an expression. c) Substituting a character string in a text expression with a specified string. d) Combining more than 2 columns or expressions into a single column in the output/a) Displaying a date in a nondefault format.
    b) Finding the number of characters in an expression.
    c) Substituting a character string in a text expression with a specified string.
  138. 138Which Normal Form is a table in if it has no multi-valued attributes and no partial dependencies? a) 1NF b) 2NF c) 3NF d) 4NFb) 2NF
  139. 139ORDER BYORDER BY
  140. 140SORTINGSORTING
  141. 141JOINING TABLESJOINING TABLES
  142. 142EQUIJOINEQUIJOIN
  143. 143module 2 retrieving datathe order by clause
  144. 144CARTESIAN PRODUCT Avoid. Can crash system. Duplicate returns.CARTESIAN PRODUCT Avoid. Can crash system. Duplicate returns.
  145. 145OUTER JOINS equijoin + null values (+) if (+) on Right of WHERE clause, it points to the Left side of the FROM clause. If (+) on LEFT of WHERE clause, it points to the RIGHT side of FROM clause.OUTER JOINS equijoin + null values (+) if (+) on Right of WHERE clause, it points to the Left side of the FROM clause. If (+) on LEFT of ...
  146. 146LEFT AND RIGHT OUTER JOINSLEFT AND RIGHT OUTER JOINS
  147. 147RIGHT OUTER JOINRIGHT OUTER JOIN
  148. 148INNER JOININNER JOIN
  149. 149LEFT AND RIGHT OUTER JOINS Left Joins (adds Null values to Left Column) Right Join (adds Null values to R Column) Outer = Non-Equi, or Outside Joining dataLEFT AND RIGHT OUTER JOINS Left Joins (adds Null values to Left Column) Right Join (adds Null values to R Column) Outer = Non-Equi, or Ou...
  150. 150FULL OUTER JOIN Adds NULLS from BOTH COUMNSFULL OUTER JOIN Adds NULLS from BOTH COUMNS
  151. 151SINGLE ROW FUNCTIONSSINGLE ROW FUNCTIONS
  152. 152LOWER FUNCTION converts text to lower caseLOWER FUNCTION converts text to lower case
  153. 153UPPER FUNCTION converts to upper caseUPPER FUNCTION converts to upper case
  154. 154CONCATENATE FUNCTIONCONCATENATE FUNCTION
  155. 155INITCAP Initial Capital changes text to first letter capitalizedINITCAP Initial Capital changes text to first letter capitalized
  156. 156CONCAT functionCONCAT function
  157. 157CHR functionCHR function
  158. 158LENGTH functionLENGTH function
  159. 159TRIM function cleans up inputted data entry eg. entered space before nameTRIM function cleans up inputted data entry eg. entered space before name
  160. 160INSTR FUNCTION (In String)INSTR FUNCTION (In String)
  161. 161ROUND () FUNCTION rounds numbersROUND () FUNCTION rounds numbers
  162. 162TRUNCATE function chops off to precise decimal (does not round)TRUNCATE function chops off to precise decimal (does not round)
  163. 163TO DATE FUNCTIONTO DATE FUNCTION
  164. 164TO_CHAR function converts data to a string formatTO_CHAR function converts data to a string format
  165. 165AVG FUNCTION AGGREGATEAVG FUNCTION AGGREGATE
  166. 166MATH ON AGGREGATE FUNCTIONSMATH ON AGGREGATE FUNCTIONS
  167. 167AVG with DISTINCT (if there's any repeated values.. it will not go into AVG function)AVG with DISTINCT (if there's any repeated values.. it will not go into AVG function)
  168. 168COUNT functionCOUNT function
  169. 169MIN() MAX() FUNCTIONSMIN() MAX() FUNCTIONS
  170. 170STANDARD DEVIATION functionSTANDARD DEVIATION function
  171. 171SUM() Variance FunctionSUM() Variance Function
  172. 172VARIANCE functionVARIANCE function
  173. 173GROUP BYGROUP BY
  174. 174GROUP BY multiple columnsGROUP BY multiple columns
  175. 175HAVINGHAVING
  176. 176COUNTING GROUPS OF ROWSCOUNTING GROUPS OF ROWS
  177. 177TYPE MISMATCHTYPE MISMATCH
  178. 178CONVERSION FUNCTIONSCONVERSION FUNCTIONS
  179. 179More Uses of TO_CHARMore Uses of TO_CHAR
  180. 180More Uses of TO_CHARMore Uses of TO_CHAR
  181. 181TO DATETO DATE
  182. 182TWO-DIGIT YEAR CONVERSIONTWO-DIGIT YEAR CONVERSION
  183. 183TO DATE CONVERSIONTO DATE CONVERSION
  184. 184TO NUMBERTO NUMBER
  185. 185DATE TIMEDATE TIME
  186. 186ADD MONTHSADD MONTHS
  187. 187LAST DAYLAST DAY
  188. 188MONTHS BETWEENMONTHS BETWEEN
  189. 189NEXT DAYNEXT DAY
  190. 190NUMERIC FUNCTIONSNUMERIC FUNCTIONS
  191. 191ABSOLUTE VALUE ABSABSOLUTE VALUE ABS
  192. 192CEIL() FLOOR()CEIL() FLOOR()
  193. 193GREATESTGREATEST
  194. 194POWERPOWER
  195. 195square root SQRTsquare root SQRT
  196. 196ROUNDROUND
  197. 197TYPES OF SUBQUERIESTYPES OF SUBQUERIES
  198. 198SINGLE-ROWSINGLE-ROW
  199. 199USING DIFFERENT OPERATORS in the WHERE clauseUSING DIFFERENT OPERATORS in the WHERE clause
  200. 200USING A SUBQUERY in a FROM clause (inline View)USING A SUBQUERY in a FROM clause (inline View)

Information

Description
SIT772 Flashcards on SIT772 - SQL, created by Amber Saxon on 25/11/2019.
Created by
Amber Saxon
Views
1
Rating

Feedback

What is it about?

What do you think of the new look?

How do you like it?