Week 5

Question 1

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

Answer 1

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

Question 2

DDL

Answer 2

Data definiton language

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

Question 3

DML

Answer 3

data manipulation language

commands that mainitan and query a db

Question 4

DCL

Answer 4

data control language

commands the contral db, administer priveleges and committ data

Question 5

example of ddl

Answer 5

create tables

establish fk

drop or truncate tables

PHYSICAL DESIGN & MAINTENACNE

Question 6

emaple of dml

Answer 6

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

iml & maintenance

Question 7

DCL example

Answer 7

contrl database
GRANT ADD REVOKE

imp and maintenance

Question 8

major create statement

Answer 8

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

Question 9

other create statments

Question 10

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

Answer 10

this is the formatting!!!

CREATE TABLE name(
___ data type PK not null,
)

Question 11

HOW CAN WE ALTER TABLES

Answer 11

Change oclumn name, add a new column, drop a column

Question 12

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

Answer 12

just a comma!! after each

Question 13

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

Answer 13

;

Question 14

How to alter a table to add?

Answer 14

ALTER TABLE name
ADD __data type Not Null;

Question 15

How to alter a table to drop

Answer 15

ALTER TABLE name
DROP name;

Question 16

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

Answer 16

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

Question 17

5 types of constraints

Answer 17

NOT NULL

CHECK (like >= a value)

UNQIUE

PRIMARY KEY

FOREIGN KEY

Question 18

how to add a constraint?

Answer 18

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

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

Question 19

MAIN CODES

Answer 19

CREATE TABLE__

ALTER TABLE___

Question 20

How to drop a component ?

Answer 20

ALTER TABLE __name__
DROP __Component Type__ __ComponentName__;

COMPOENNENT TYPE: COLUMN

Question 21

Insert statemetn

Answer 21

Adds one or more rows to a table or view

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

Question 22

UPDATE statment , no where clause

Answer 22

Changes existing data in atable or view

UPDATE name
SET Compinentype = ??

UPDATE table
SET ColC=6;

Question 23

UDATE STATEMTN, WITH WEHRE CLAUSE!

Answer 23

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

Question 24

DELETE STATEMETN

Answer 24

removes one or more rows from a table

DELET FROM name
WHERE columnA=3

Question 25

How to change delete to verify rows to delete

Answer 25

SELECT!!! FIRST

SELECET*FROM table
WHERE colA=5

then delete

Question 26

6 clauses of select statement

Answer 26

SELECT
FROM
WHERE
GROUPBY
HAVING
ORDER BY

Question 27

Basic select statement

Answer 27

SELECT… FROM

SELECT column1,column2 FROM
name of object to retriev data from;

SELECT* FROM table;

*=ALL

Question 28

How to do column/table alishing

Answer 28

colum name as someting else
or table name as something else

SELECT columns AS [col1]
FROM table_1 AS t

Question 29

How to use expressions

Answer 29

SELECT col1,col2,cold3 AS

go one from using expresisons

Question 30

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

Answer 30

ORDER BY

Question 31

Which of the following can produce scalar and vector aggregates?

Answer 31

GROUP BY

Question 32

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

Answer 32

The SELECT clause is processed before the ORDER BY clause.

Question 33

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

Answer 33

SCALAR AGGREGATE

Question 34

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

Answer 34

FROM

Question 35

FROM -> SELECT-> ORDEER BY

Question 36

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

Answer 36

identify each attribute and its characteristics.

Question 37

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

Answer 37

ORDER BY

Question 38

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.

Question 39

priority of operators

Answer 39

not->and -> or

NAO! like bedmas

Question 40

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

Answer 40

not( p.productfinish = ‘cherry’)

Question 41

BETWEEN?

Answer 41

simploifying query !

between 200 and 300

Question 42

how can u use in in a list

Answer 42

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

Question 43

hOW Do you maek a new column?

Answer 43

just make it lol

select * year(e.employeebday)
from employee_T e

Question 44

how to give anew columna name

Answer 44

[Name]

Question 45

what does top do?

Answer 45

select top rows or top 50%

preivew table content

Question 46

How to add an attribute to the table?

Answer 46

This is like adding a column!

ALTER TABLE name
ADD (column name) (data type)

Question 47

WHAT DOES LIKE DO?

Answer 47

PATTERN MATCHING! LIKE USING WILDCARDS

Question 48

How to do anything that is descedning or ascending?

Answer 48

use order by

ORDER BY T.column DESC|aSC

or if multiple:
ORDER BY name, name, name

Question 49

when to use group by?

Answer 49

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().

Question 50

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.

Question 51

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.

Answer 51

SFJWGHOL

SIMON FRASER JUNIOR WON GAY HOLISTER

Question 52

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.

Question 53

HAVING: Filters groups after aggregation.

Answer 53

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