Week 5 Flashcards by Unknown Unknown

two components of applicaiton
how does sql link
what does it link to

grpahical interface and direct query
sql is used to pull info out of
DBMS

How well did you know this?

Not at all

Perfectly

DDL

Data definiton language

commands that define a database, including creating, altering, and dropping tables/establish constraintsML

How well did you know this?

Not at all

Perfectly

DML

data manipulation language

commands that mainitan and query a db

How well did you know this?

Not at all

Perfectly

DCL

data control language

commands the contral db, administer priveleges and committ data

How well did you know this?

Not at all

Perfectly

example of ddl

create tables

establish fk

drop or truncate tables

PHYSICAL DESIGN & MAINTENACNE

How well did you know this?

Not at all

Perfectly

emaple of dml

load the database: insert data, update data, manipulate data with SELECT

implenentation & maintenance

How well did you know this?

Not at all

Perfectly

DCL example

contrl database
GRANT ADD REVOKE

imp and maintenance

How well did you know this?

Not at all

Perfectly

major create statements

CREATE schema- define portion of db owened by 1 user

CREATE table- define anew table and its columns

CREATE VIEW- defines logical table from one or more table or views

How well did you know this?

Not at all

Perfectly

other create statments

create character, set, collation , translation..

How well did you know this?

Not at all

Perfectly

CREATE TABLE Test1_T
(
Test1ID int PRIMARY KEY,
FirstName varchar(40) NOT NULL,
LastName varchar(30) NOT NULL,
State char(2) NULL
);

this is the formatting!!!

CREATE TABLE name(
(name) type PK (constraint),
(name) type (constraint),
)

How well did you know this?

Not at all

Perfectly

HOW CAN WE ALTER TABLES

Change oclumn name, add a new column, drop/DELETE a column, change the columns like its null or not null

How well did you know this?

Not at all

Perfectly

How to end off the diff rows in the the code?

just a comma!! after each

How well did you know this?

Not at all

Perfectly

How to end off the different sectins of code? the cunkS?

;

How well did you know this?

Not at all

Perfectly

How to alter a table to add?

ALTER TABLE name
ADD (name) type Not Null;

ALTER TABLE name
DROP COLUMN (name);

ALTER TABLE name
ALTER COLUMN (name) type Not Null;

How well did you know this?

Not at all

Perfectly

How to alter a table to drop

ALTER TABLE name
DROP name;

How well did you know this?

Not at all

Perfectly

How to alter table to alter column like the data type?

ALTER TABLE name
ALTER COLUMN __ varchar(#) Null;

How well did you know this?

Not at all

Perfectly

5 types of constraints

NOT NULL

CHECK (like >= a value)

UNQIUE

PRIMARY KEY

FOREIGN KEY

How well did you know this?

Not at all

Perfectly

how to add a constraint?

ALTER TABLE Test2_T
ADD CONSTRAINT FK_Test2_T_Test1_T FOREIGN KEY
(EMPLOYEEID)
REFERENCES Test1_T(TestID);

ALTER TABLE ___name___
ADD CONSTRAINT __name of constraint___ FOREIGN KEY
Type the foreign key you wanna add
REFERENCES Table(Column)

How well did you know this?

Not at all

Perfectly

MAIN CODES

CREATE TABLE__

ALTER TABLE___

How well did you know this?

Not at all

Perfectly

How to drop a component ?

ALTER TABLE __name__
DROP __Type___ _Name__;

COMPOENNENT TYPE: COLUMN, CONSTRAINT, INDEX

How well did you know this?

Not at all

Perfectly

Insert statemetn

Adds one or more rows to a table or view

INSERT INTO table
(ColA, ColB, ColC) VALUES (1,2,3)

INSERT INTO __table__
Values (1,2,3)

How well did you know this?

Not at all

Perfectly

UPDATE statment , no where clause

Changes existing data in atable or view

UPDATE name
SET Compinentype = ??

UPDATE table
SET ColC=6;

How well did you know this?

Not at all

Perfectly

UDATE STATEMTN, WITH WEHRE CLAUSE!

UPDATE table name
SET ColC=10, ColB=20,
WHERE ColA= 2;

How well did you know this?

Not at all

Perfectly

DELETE STATEMETN

removes one or more rows from a table!! not columns

DELET FROM name
WHERE columnA=3

How well did you know this?

Not at all

Perfectly

How to change delete to verify rows to delete

SELECT!!! FIRST SELECET*FROM table WHERE colA=5 then delete

6 clauses of select statement

SELECT FROM WHERE GROUPBY HAVING ORDER BY

Basic select statement

SELECT... FROM SELECT column1,column2 FROM name of object to retriev data from; SELECT* FROM table; *=ALL

How to do column/table alishing

colum name as someting else or table name as something else SELECT columns AS [col1] FROM table_1 AS t

How to use expressions

SELECT col1,col2,cold3 AS go one from using expresisons

The last part of an SQL query to be read is the ________ statement.

ORDER BY

Which of the following can produce scalar and vector aggregates?

GROUP BY

Which of the following is true of the order in which SQL statements are evaluated?

The SELECT clause is processed before the ORDER BY clause.

A single value returned from an SQL query that includes an aggregate function is called a(n):

SCALAR AGGREGATE

The first part of an SQL query to be read is the ________ statement.

FROM

FROM -> SELECT-> ORDEER BY

The first in a series of steps to follow when creating a table is to:

identify each attribute and its characteristics.

To get all the customers from Hawaii sorted together, which of the following would be used?

ORDER BY

LIKE: Used for pattern matching. For example, LIKE 'L%' finds all employee names that start with "L". The % is a wildcard that matches any sequence of characters. IS: Used for comparison, typically with NULL values or boolean expressions (e.g., IS NULL, IS TRUE). It cannot be used for pattern matching.

priority of operators

not->and -> or NAO! like bedmas

how do you say "product finish is not equal to cherry?"

not( p.productfinish = 'cherry')

BETWEEN?

simploifying query ! between 200 and 300

how can u use in in a list

select * from customer_t as c where c.customer.state IN ('FL','MI'..)

hOW Do you maek a new column?

just make it lol select * year(e.employeebday) from employee_T e

how to give anew columna name

[Name]

what does top do?

select top rows or top 50% preivew table content

How to add an attribute to the table?

This is like adding a column! ALTER TABLE name ADD (column name) (data type)

WHAT DOES LIKE DO?

PATTERN MATCHING! LIKE USING WILDCARDS

How to do anything that is descedning or ascending?

use order by ORDER BY T.column DESC|aSC or if multiple: ORDER BY name, name, name

when to use group by?

IF U USE MATHEMATICAL FUNCTIONS! DONT USE ORDER BY! GROUP BY Purpose: Used to aggregate data based on one or more columns. It groups rows that have the same values in specified columns into summary rows. Common Functions: Often used with aggregate functions like SUM(), COUNT(), AVG(), MAX(), and MIN().

se GROUP BY: When you need to aggregate data. To create summaries or counts of records. Use ORDER BY: When you want to sort the results of a query. To display data in a particular sequence, regardless of whether it's grouped or not.

SELECT: Specifies the columns you want to retrieve. FROM: Indicates the tables from which to retrieve the data. JOIN: (if applicable) Specifies how to join additional tables. WHERE: Filters rows before aggregation. GROUP BY: Groups rows sharing a property so aggregate functions can be applied. HAVING: Filters groups after aggregation. ORDER BY: Sorts the final result set. LIMIT / OFFSET: (if applicable) Specifies the number of records to return or skips a certain number.

SFJWGHOL SIMON FRASER JUNIOR WON GAY HOLISTER

DISTINCT is used in SQL to return only unique values from a result set. Here are some scenarios and guidelines for when to use it: When to Use DISTINCT: Eliminating Duplicates: When you want to remove duplicate rows from your query results. Example: Retrieving a list of unique product categories from a sales table.

HAVING: Filters groups after aggregation.

SELECT Department, AVG(Salary) AS AvgSalary FROM Employees GROUP BY Department HAVING AVG(Salary) > 60000;

MySql

opensource rmbms owned by oraccle

PL/SQL

oracle xtended sql

PostgreSQL

open source included in linux

Transact SQL

*********8 this is the sql used by ms sql server

what is count(*)?

Counts all rows! including null

NUANCE OF EVERY GROUP BY CLAUSE!

EVERY NON-FUNCTION CLAUSE IN THE SELECT STATMENT SHOULD BE IN THE GROUP BY STATEMETN

INSERT INTO is an example of ________ code.

dml

Which of the following is a technique for optimizing the internal performance of the relational data model?

CLUSTERING DATA

Which of the following can produce scalar and vector aggregates?

GROUP BY

Indexes are created in most RDBMSs to:

provide rapid random and sequential access to base-table data.

To get all the customers from Hawaii sorted together, which of the following would be use

ORDER BY

What will result from the following SQL Select statement? SELECT MIN(Product_Description) FROM Product_V;

The first product description alphabetically in Product_V will be shown.

Which of the following finds all groups meeting stated conditions?

HAVING