Brainscape's Knowledge GenomeTM

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


See full index

Mine > SQL > Flashcards

SQL Flashcards

1
Q

On a mac, where is the data for the mysql databases located?

A

/usr/local/mysql/data

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

On the mysql cli, what does ‘status;’ give you.

A

Bunch of information like ‘Server version’

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

How do you change the password for root for mysql?

A

mysqladmin -u root -h host_name password “newpwd”

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

What mysql table has the following columns?

table_schema
table_name
column_type
column_name

A

information_schema.columns

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

Show an example SQL insert statement with column names.

A

INSERT INTO table_name (column1, column2, column3,…)
VALUES (value1, value2, value3,…)

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

How do you ‘just’ create a new user in mysql?

A

CREATE USER ‘user1’@’localhost’ IDENTIFIED BY ‘pass1’;

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

Suppose you have a data file named importfile.csv that contains 3 comma separated columns of data on each line.

You want to import this textfile into your MySQL table named test_table, which has 3 columns that are named field1, field2 and field3.

A

LOAD DATA LOCAL INFILE ‘/importfile.csv’
INTO TABLE test_table
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
(field1, filed2, field3);

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

Show an example using date_format.

A

mysql> SELECT date_format(‘2009-10-04 22:23:00’, ‘%Y-%c-%d’);
-> 2009-10-04

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

How do you find the database version of the mysql database?

Show on the command line and in the CLI

A

while in the cli: status;

on the command line: mysql -u root -p -e status|grep ‘Server version’

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

How do you do a SELECT INTO in mysql?

A

Example:
INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

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

How did I do a dump to get specific imgids?

A

mysqldump -u rc -pshop shopImagesTest product_images –compact –where=”

imgid in (
‘yimgdacc4574822f639002581f901787’,
…
);
“

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

What query can you use to find the size of a database in mysql?

A

SELECT table_schema “Data Base Name”,
sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”
FROM information_schema.TABLES GROUP BY table_schema ;

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

What are the 4 different ways you can query in GORM?

A

dynamic finders
criteria queries
query by example
HQL and SQL

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

What’s an easy way of seeing if mysql is running?

A

mysqladmin version

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

How can you see what engine is backing a mysql database table?

(the preferred way)

A

show table status like ‘feed_latest’\G

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

How do you delete a user in mysql?

A

drop user ‘example_user’@’localhost’;

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

Where are mysql command history stored?

A

~/.mysql_history

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

How does the following work in a SQL injection attack?

Username: admin’–

A

SELECT * FROM members WHERE username = ‘admin’–’ AND password = ‘password’

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

What can you set in order to have the console print all the database queries that Hibernate issues?

A

Set ‘logSql=true’
in
DataSource.groovy

19
Q

How can you run a mysql script?

(three ways)

A

mysql [my_db_name] -u [user_name] -p[password] < mysql.sql

OR

mysql> source /path/to/Test.sql

OR

mysql> source ./path/to/Test.sql (relative path from mysql.exe)

20
Q

LOAD DATA LOCAL INFILE ‘/importfile.csv’
INTO TABLE test_table
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
(field1, filed2, field3);

A

Suppose you have a data file named importfile.csv that contains 3 comma separated columns of data on each line. You want to import this textfile into your MySQL table named test_table, which has 3 columns that are named field1, field2 and field3.

21
Q

Describe TRUNCATE TABLE for mysql.

A

TRUNCATE TABLE empties a table completely. Logically, this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

22
Q

How to connect to a mysql server?

A

mysql -u rc -pshop -h snapshot01 product_ro_0

23
Q

How can you see all the users that have been created on the system?

A

SELECT user, host FROM mysql.user;

24
Q

How to do a mysql dump to get the schema?

A

mysqldump –no-data -u rc -pshop -h snapshot01 product_ro_0 > dump.schema.sql

25
Q

how do I find the top number of results (mysql)

A

SELECT column_name(s)
FROM table_name
LIMIT number

26
Q

How can you create a new user and give them priviledges in one line in mysql?

A

GRANT ALL ON *.* TO ‘user2’@’localhost’ IDENTIFIED BY ‘pass1’;

27
Q

How do you give priviledges to a user in mysql?

A

GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO ‘user1’@’localhost’;

28
Q

How do you find the column names in mysql?

A

desc <table_name>;

29
Q

How do you cancel a command in mysql?

A

\c

30
Q

How do you see what databases are available in mysql?

A

show databases;

31
Q

How do you know what database you are currently using in mysql?

A

select database();

32
Q

How do you delete a database?

A

mysql> drop database [database name];

33
Q

Where do you put your mysql preferences?

A

~/.my.cnf

34
Q

The following syntax is for oracle and postgres. How would you do the same quote in mySQL?

select imgurl || ‘/’ || imgid || ‘_’ || dimension || ‘.jpg’ from product_images;

A 
select concat(imgurl , '/' , imgid , '\_' , dimension , '.jpg') 
from product\_images;
35
Q

What is the difference between a “where” clause and a “having” clause?

A

“Where” is a kind of restiriction statement. You use where clause to restrict all the data from DB.Where clause is using before result retrieving. But Having clause is using after retrieving the data.Having clause is a kind of filtering command.

36
Q

What are the tradeoffs with having indexes?

A
  1. Faster selects, slower updates.
  2. Extra storage space to store indexes. Updates are slower because in addition to updating the table you have to update the index.
37
Q

What is a “join”?

A

‘join’ used to connect two or more tables logically with or without common field.

38
Q

What is “normalization”? “Denormalization”? Why do you sometimes want to denormalize?

A

Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation. This is done by reduction in the number of joins needed for data processing.

39
Q

What types of index data structures can you have?

A

An index helps to faster search values in tables. The three most commonly used index-types are:

  • B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases.
  • Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only few space and is very fast.(however, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD)
  • Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.
40
Q

What is a “functional dependency”? How does it relate to database table design?

A

Functional dependency relates to how one object depends upon the other in the database. for example, procedure/function sp2 may be called by procedure sp1. Then we say that sp1 has functional dependency on sp2.

41
Q

What is a “trigger”?

A

Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurance of one of the data-modification operations. A trigger is a database object directly associated with a particular table. It fires whenever a specific statement/type of statement is issued against that table. The types of statements are insert,update,delete and query statements.

Basically, trigger is a set of SQL statements A trigger is a solution to the restrictions of a constraint. For instance:

  1. A database column cannot carry PSEUDO columns as criteria where a trigger can.
  2. A database constraint cannot refer old and new values for a row where a trigger can.
42
Q

Why can a “group by” or “order by” clause be expensive to process?

A

Processing of “group by” or “order by” clause often requires creation of Temporary tables to process the results of the query. Which depending of the result set can be very expensive.

43
Q

What is “index covering” of a query?

A

Index covering means that “Data can be found only using indexes, without touching the tables”

44
Q

What is a SQL view?

A

An output of a query can be stored as a view. View acts like small table which meets our criterion. View is a precomplied SQL query which is used to select data from one or more tables. A view is like a table but it doesn’t physically take any space. View is a good way to present data in a particular format if you use that query quite often. View can also be used to restrict users from accessing the tables directly.

45
Q

How do you do a ‘self join’ version of the following query:

SELECT employee_name
FROM employee
WHERE employee_location in
( SELECT employee_location
FROM employee
WHERE employee_name = “Joe”)

A

SELECT e1.employee_name
FROM employee e1, employee e2
WHERE e1.employee_location = e2.employee_location
AND e2.employee_name=”Joe”;

46
Q

how would you retrieve the unique values for the employee_location without using the DISTINCT keyword?

table: employee(name, employee_location)

A

SELECT employee_location from employee
GROUP BY employee_location

Mine (6 decks)

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