1 CREATE PROCEDURE P () AS
2 BEGIN
3 END;
1 CREATE PROCEDURE P ()
2 BEGIN
3 END
1 CREATE PROCP
2 BEGIN
3 END;
1 CREATE PROC P AS O
2 BEGIN
3 END;
CREATE TABLE employee (
employeeID char(10),
firstName varchar(50),
lastName varchar(50),
phone varchar(20),
address varchar(50),
PRIMARY KEY ON employeeID
);
CREATE TABLE employee (
employeeID char(10),
firstName varchar(50),
lastName varchar(50),
phone varchar(20),
address varchar(50),
PRIMARY KEY employeeID
);
CREATE TABLE IF EXISTS employee (
employeeID char(10),
firstName varchar(50),
lastName varchar(50),
phone varchar(20),
address varchar(50),
PRIMARY KEY (employeeID)
);
CREATE TABLE IF NOT EXISTS employee (
employeeID char(10),
firstName varchar(50),
lastName varchar(50),
phone varchar(20),
address varchar(50),
PRIMARY KEY (employeeID)
);
SELECT * FROM Employees WHERE EmployeeName LIKE 'a%'
SELECT * FROM clients; what
does
clients represent?
cat file | mysqlLOAD DATA INFILELOAD DATA LOCAL INFILEextended INSERT statementinsert into cars (make, model, year) values ('Ford', 'Mustang', 2002) ('Mercedes', 'C', 2003)
insert into cars (make, model, year) values ('Ford', 'Mustang', 2002) values ('Mercedes', 'C', 2003)
insert into cars (make, model, year) extended ('Ford', 'Mustang', 2002), ('Mercedes', 'C', 2003)
insert into cars (make, model, year) values ('Ford', 'Mustang', 2002), ('Mercedes', 'C', 2003)
Note that the question is about getting the data and not about the duplicating operation
itself.
And actually there is no need to run SHOW CREATE TABLE at all. To duplicate the table
structure
you can CREATE TABLE new_table LIKE original_table;.
CREATE TEMPORARY TABLE customers;DROP TEMP TABLE customers;DROP TABLE customers;DROP TEMPORARY TABLE customers;This is exactly what WITH clause is designed for
perror
command-line utility used for?Note: perror prints a description for a system error code or for a storage engine (table handler) error code - link
Note: DESCRIBE tablename is a shortcut for this command
Note: "to make the system faster" can also be correct. For example we can calculate some heavy query in advance and store its result in some column (use it as a cache). So if "system" means "application which uses mysql" then it's correct too.
LOAD XML LOCAL INFILE 'cars.xml'
INTO TABLE cars
ROWS IDENTIFIED BY `<car>`;
<car>
<field name="make"> Lexus </field>
<field name="model"> IS300 </field>
<field name="make"> 2016 </field>
</car>
<car name="make"> Dodge </car>
<car name="model"> Ram </car>
<car name="year"> 2000 </car>
<car make="Ford" model="Mustang" year="2002"/>
<car year="2010">
<make>Mercedes</make> <model> C-Class</model>
</car>
INSERT IGNOREINSERT UNIQUEINSERT INTOINSERT DISTINCTTRUNCATE TABLE statement is true?
DELETE triggers
associated
with the table.Note: both answers are correct - see TRUNCATE TABLE Statement in MySQL manual
SELECT state, COUNT(*) FROM customers WHERE ID IN (SELECT customerID FROM purchases) GROUP BY state;
SELECT state, COUNT(*) FROM customers c LEFT JOIN purchases p ON c.ID = p.customerID GROUP BY state;
SELECT state, COUNT(*) FROM customers c, purchases p WHERE c.ID = p.customerID GROUP BY state;
SELECT state, COUNT(*) FROM customers GROUP BY state;Explanation: THe difference between 2 and 3 is that LEFT JOIN will return 1 row per customer before grouping. If replaced with RIGHT JOIN it would return the correct info.
DELETE DUPSDELETE DISTINCTDELETE JOINDELETE WITHDEFAULT valueRETURN variableSQLEXCEPTION routineNOT FOUND handlermysqldmysqlmysqladminmysqldumpSELECT city FROM json_data;SELECT city->>'$.name' city FROM json_data;
SELECT city.name city FROM json_data;SELECT city->'$.name' city FROM json_data;Note: the last option is valid too but the results will be enclosed with quotation marks
Table name: customers
| ID | lastname | firstname | phone | address | city | state | zip |
|---|---|---|---|---|---|---|---|
| A001 | Smith | Bob | 212-555-1212 | 1001 1st Street | New York | NY | 10001 |
| A002 | Chang | John | 213-555-5678 | 888 Rodeo Drive | Los Angeles | CA | 90210 |
| A003 | Smith | Mary | 999-999-9999 | 123 Main Street | Anytown | VA | 12345 |
| A004 | Johnson | Jack | 312-312-3120 | 1111 Chicago Avenue | Chicago | IL | 60606 |
| A005 | Lopez | Linda | 737-777-3333 | 123 Main Street | Austin | TX | 73344 |
SELECT *
FROM customers
WHERE address MATCH 'Street' OR 'Drive';
SELECT *
FROM customers
WHERE MATCH(address) IN ('street, drive');
SELECT *
FROM customers
WHERE address MATCH 'Street' OR address MATCH 'Drive';
SELECT *
FROM customers
WHERE MATCH(address) AGAINST ('street, drive');
DISPLAY TRIGGERS;SHOW TRIGGERS;SELECT ALL TRIGGERS;SELECT * FROM information_schema.triggers;mysqladmin flush-threadsmysqladmin flush-tablesmysqladmin flush-privilegesmysqladmin flush-allSELECT * FROM customers WHERE PhoneNumber = NULL;SELECT * FROM customers WHERE PhoneNumber IS NOT VALID;
SELECT * FROM customers WHERE PhoneNumber IS NULL;
SELECT * FROM customers WHERE PhoneNumber IS UNKNOWN;
not an
available string type for a column?ENUMSETBITCHARExplnation: BIT is not a string type
SELECTUSESETDECLAREExplanation: Both SET and DECLARE are used to create variables. Reference: MySQL STORED PROCEDURE Tutorial With
Examples
CHAR(10) data type is more efficient and
space-saving.Explanation: CTEs do not create temporary tables, they only work within a signle query. Reference: 13.2.15 WITH (Common Table Expressions).
Reference: 4.2.2.4 Program Option Modifiers
SELECT name FROM students WHERE name REGEXP '^to';
CREATE VIEW v1 SELECT * FROM t1 WHERE col1 > 10;CREATE VIEW v1 AS BEGIN SELECT * FROM t1 END;CREATE VIEW v1 BEGIN SELECT * FROM t1 END;CREATE VIEW v1 AS SELECT * FROM t1;UNDOUNCOMMITROLLBACKREVERSECreate table size (ENUM ('Small','Medium','Large'));
Create table ENUM (name ('Small','Medium','Large'));
Create table size (name: ENUM['Small','Medium','Large']);
Create table size (name ENUM('Small','Medium','Large'));
SELECT cars.*, purchases.date
FROM cars RIGHT JOIN purchases
ON cars.ID = purchases.carID;
SELECT cars.*, purchases.date
FROM cars INNER JOIN purchases
ON cars.ID = purchases.carID;
SELECT cars.*, purchases.date
FROM cars JOIN purchases
ON cars.ID = purchases.carID;
SELECT cars.*, purchases.date FROM cars LEFT JOIN purchases ON cars.ID = purchases.carID;
IF var1 THEN SET varA = var1;
ELSEIF var2 THEN SET varA = var2;
ELSEIF var3 THEN SET varA = var3;
ELSE SET varA = var4;
END IF;
IF var1 = var2 THEN SET varA = var1;
ELSEIF var2 = var3 THEN SET varA = var2;
ELSEIF var3 = var4 THEN SET varA = var3;
ELSE SET varA = var4;
END IF;
IF var1 = 1 THEN SET varA = var1;
ELSEIF var2 = 2 THEN SET varA = var2;
ELSEIF var3 = 3 THEN SET varA = var3;
ELSE SET varA = var4;
END IF;
IF var1 = 1 THEN SET varA = var1;
ELSEIF var1 = 2 THEN SET varA = var2;
ELSEIF var1 = 3 THEN SET varA = var3;
ELSE SET varA = var4;
END IF;
Table name: superheroes
| name | alias | power1 | power2 | power3 |
|---|---|---|---|---|
| Superman | Clark Kent | Flight | X-Ray Vision | Super Strength |
| Wonder Woman | Diana Prince | Force Fields | Reflexes | Telepathy |
| Spider-man | Peter Parker | Walcrawling | Web-making | Enhanced Senses |
| Aquaman | Arthur Curry | Underwater Breathing | Enhanced Sight | Stamina |
| Hulk | Bruce Banner | Super Strength | Radiation Immunity | Invulnerability |
SELECT * FROM customers WHERE UPPEERCASE(LastName) = 'POTTER';
SELECT * FROM customers WHERE LOWERCASE(LastName) = 'potter';
SELECT * FROM customers WHERE UPPER(LastName) = 'POTTER';
SELECT * FROM customers WHERE UPPER(LastName) = 'Potter';
SELECT emp_id, fname, lname
FROM employee
WHERE title=’HEAD TELLER’ AND start_date>2008-11-23;
SELECT e.emp_id, e.fname,e.lname,d.name
FROM employee e INNER JOIN department d
ON e.dept_id=e.dept_id;
SELECT LastName>'Hansen' AND LastName<'Pettersen' FROM Persons
SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'
SELECT * FROM Persons WHERE LastName>'Hansen' AND LastName<'Pettersen'
None of the above. Students : (Roll number, Name, Date of birth)
Courses: (Course number, Course name, instructor)
Grades: (Roll number, Course number, Grade)
SELECT DISTINCT Name
FROM Students, Courses, Grades
WHERE Students.Roll_number = Grades.Roll_number
AND Courses.Instructor =Sriram
AND Courses.Course_number = Grades.Course_number
AND Grades.Grade = A
(Which of the following sets is computed by the above query?)
IF EXISTS and CROSS JOIN are not valid for a trigger.