Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

BTMA 331 > Week 5 > Flashcards

Week 5 Flashcards

1
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

DDL

A

Data definiton language

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

DML

A

data manipulation language

commands that mainitan and query a db

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

DCL

A

data control language

commands the contral db, administer priveleges and committ data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

example of ddl

A

create tables

establish fk

drop or truncate tables

PHYSICAL DESIGN & MAINTENACNE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

emaple of dml

A

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

implenentation & maintenance

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

DCL example

A

contrl database
GRANT ADD REVOKE

imp and maintenance

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

major create statements

A

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?
1
Not at all
2
3
4
5
Perfectly
9
Q

other create statments

A

create character, set, collation , translation..

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

this is the formatting!!!

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

HOW CAN WE ALTER TABLES

A

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?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

just a comma!! after each

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

How to alter a table to add?

A

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?
1
Not at all
2
3
4
5
Perfectly
15
Q

How to alter a table to drop

A

ALTER TABLE name
DROP name;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

5 types of constraints

A

NOT NULL

CHECK (like >= a value)

UNQIUE

PRIMARY KEY

FOREIGN KEY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

how to add a constraint?

A

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)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

MAIN CODES

A

CREATE TABLE__

ALTER TABLE___

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

How to drop a component ?

A

ALTER TABLE __name__
DROP __Type___ _Name__;

COMPOENNENT TYPE: COLUMN, CONSTRAINT, INDEX

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Insert statemetn

A

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?
1
Not at all
2
3
4
5
Perfectly
22
Q

UPDATE statment , no where clause

A

Changes existing data in atable or view

UPDATE name
SET Compinentype = ??

UPDATE table
SET ColC=6;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

UDATE STATEMTN, WITH WEHRE CLAUSE!

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

DELETE STATEMETN

A

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

DELET FROM name
WHERE columnA=3

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

How to change delete to verify rows to delete

A

SELECT!!! FIRST

SELECET*FROM table
WHERE colA=5

then delete

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

6 clauses of select statement

A

SELECT
FROM
WHERE
GROUPBY
HAVING
ORDER BY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Basic select statement

A

SELECT… FROM

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

SELECT* FROM table;

*=ALL

28
Q

How to do column/table alishing

A

colum name as someting else
or table name as something else

SELECT columns AS [col1]
FROM table_1 AS t

29
Q

How to use expressions

A

SELECT col1,col2,cold3 AS

go one from using expresisons

30
Q

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

A

ORDER BY

31
Q

Which of the following can produce scalar and vector aggregates?

A

GROUP BY

32
Q

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

A

The SELECT clause is processed before the ORDER BY clause.

33
Q

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

A

SCALAR AGGREGATE

34
Q

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

A

FROM

35
Q

FROM -> SELECT-> ORDEER BY

A
36
Q

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

A

identify each attribute and its characteristics.

37
Q

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

A

ORDER BY

38
Q

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.

A
39
Q

priority of operators

A

not->and -> or

NAO! like bedmas

40
Q

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

A

not( p.productfinish = ‘cherry’)

41
Q

BETWEEN?

A

simploifying query !

between 200 and 300

42
Q

how can u use in in a list

A

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

43
Q

hOW Do you maek a new column?

A

just make it lol

select * year(e.employeebday)
from employee_T e

44
Q

how to give anew columna name

A

[Name]

45
Q

what does top do?

A

select top rows or top 50%

preivew table content

46
Q

How to add an attribute to the table?

A

This is like adding a column!

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

47
Q

WHAT DOES LIKE DO?

A

PATTERN MATCHING! LIKE USING WILDCARDS

48
Q

How to do anything that is descedning or ascending?

A

use order by

ORDER BY T.column DESC|aSC

or if multiple:
ORDER BY name, name, name

49
Q

when to use group by?

A

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

50
Q

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.

A
51
Q

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.

A

SFJWGHOL

SIMON FRASER JUNIOR WON GAY HOLISTER

52
Q

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.

A
53
Q

HAVING: Filters groups after aggregation.

A

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

54
Q

MySql

A

opensource rmbms owned by oraccle

55
Q

PL/SQL

A

oracle xtended sql

56
Q

PostgreSQL

A

open source included in linux

57
Q

Transact SQL

A

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

58
Q

what is count(*)?

A

Counts all rows! including null

59
Q

NUANCE OF EVERY GROUP BY CLAUSE!

A

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

60
Q

INSERT INTO is an example of ________ code.

A

dml

61
Q

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

A

CLUSTERING DATA

62
Q

Which of the following can produce scalar and vector aggregates?

A

GROUP BY

63
Q

Indexes are created in most RDBMSs to:

A

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

64
Q

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

A

ORDER BY

65
Q

What will result from the following SQL Select statement?

SELECT MIN(Product_Description)
FROM Product_V;

A

The first product description alphabetically in Product_V will be shown.

66
Q

Which of the following finds all groups meeting stated conditions?

A

HAVING

67
Q
A

BTMA 331 (10 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions