How many tables are included in the Automotive Supplies Online Store database
The Automotive Supplies Online Store database includes 8 tables.
This question belongs to the National Open “3868 MySQL Database Applications” test.
MySQL is an open source relational database management system that was released in January 1998 in its first version using the most commonly used database management language, Structured Query Language (SQL).
MySQL (pronounced “myesscueel”, not “mysequel”) is an open source relational database management system (RDBMS) that uses Structured Query Language (SQL), the most commonly used database management language, for database management. for database management.
Because MySQL is open source, anyone can download it under the GeneralPublicLicense and modify it to suit their individual needs.MySQL is highly regarded for its speed, reliability, and adaptability. Most people agree that MySQL is the best choice for managing content without the need for transactional processing.
The MySQL relational database was released in January 1998 in its first version. It provides a fully multithreaded mode of operation using the multithreading mechanism provided by the core of the system, provides programming interfaces (APIs) for programming languages such as C, C++, Eiffel, Java, Perl, PHP, Python, and Tcl, supports a wide range of field types, and provides a full set of operators to support SELECT and WHERE operations in queries.
The MySQL development team plans to release MySQL 4.0 in mid-2001. The following new features will be available in this release: a new table definition file format, high-performance data replication, and more powerful full-text search capabilities.
After that, MySQL developers hope to provide secure data replication mechanisms, an implementation of MySQL on the BeOS operating system, and the option of periodic refreshes for delayed keywords. As time progresses, MySQL will become fully compatible with the ANSI92/ANSI99 standards.
To this day the combination of mysql and php is absolutely perfect. Many large websites also use mysql databases, and the future of mysql is very bright!
MySQL Exercises for Database Experts
createtablestu1(
Idintauto_incrementprimaryKEY,
NameVARCHAR(20)notnull,
Sexvarchar(4),
BrithDate,
departmentVARCHAR(20)notnull,
addressVARCHAR(50)
)
createtablescore(
IdINTauto_ incrementPRIMARYKEY,
stu_idint,
FOREIGNKEY(stu_id)REFERENCESstu1(Id)onDELETECASCADE,
C_nameVARCHAR(20),
gradeint
)
SELECT*fromstu1
INSERTINTOstu1VALUES(901,’Boss Zhang’,’Male’,’1985-06-05′,’Computer Science Department’,’Haidian District, Beijing’);
INSERTINTOstu1VALUES(902,’Zhang Lao Er’,’Male’,’1986-06-05′,’Department of Chinese’,’Changping District, Beijing’);
INSERTINTOstu1VALUES(903,’Zhang San’,’Female’,’1990-06-05′,’Department of Chinese’,’Hunan Province Yongzhou City’);
INSERTINTOstu1VALUES(904,’Li Si’,’Male’,’1990-06-05′,’Department of English’,’Fuxin City, Liaoning Province’);
INSERTINTOstu1VALUES(905,’Wang Wu’,’Female’,’1991-06-05′. ‘Department of English’, ‘Xiamen, Fujian Province’);
INSERTINTOstu1VALUES(906,’Wang Liu’,’Male’,’1988-06-05′,’Department of Computer Science’,’Hengyang City, Hunan Province’);
SELECT*fromScore
INSERTINTOscore(stu_id,C_name,grade)VALUES(901,’Computer’,98);
INSERTINTOscore(stu_id,C_name,grade)VALUES(901,’English’,80);
INSERTINTOscore(stu_id,C_name,grade)VALUES(902,’Computer’,65);
INSERTINTOscore(stu_id,C_name,grade)VALUES(902,’Chinese’,88);
INSERTINTOscore(stu_id,C_name,grade)VALUES(903,’Chinese’,95);
INSERTINTOscore(stu_id,C_name,grade)VALUES(904,’Computer’,70);
INSERTINTOscore(stu_id,C_name,grade)VALUES(904,’English’,92);
INSERTINTOscore(stu_id,C_name,grade)VALUES(905,’English’,94);
INSERTINTOscore(stu_id,C_name,grade)VALUES(906,’Computer’,90);
INSERTINTOscore(stu_id,C_name,grade)VALUES(906,’English’,85);
/* Query the 2nd to 4th record of student table */
SELECT*fromstu1LIMIT2,2;
/*Query the information of the students of computer department and English department from student table */
SELECT*fromstu1WHEREdepartmentin(‘ Department of Computer Science’,’Department of English’);
/*SELECT information about students aged 18~22 from student table*/
SELECTYEAR(‘1990-09-09’)
SELECTYEAR(NOW())-YEAR(‘1990-09-09’);
SELECTs.Name,s.age,s.Brithfrom(SELECT*,YEAR(NOW())-YEAR(Brith)asageFROMstu1)assWHEREs.age>23ands.age<28;
/*8. From the student table to find out how many people are in each department*/
SELECTdepartment,COUNT(id)fromstu1GROUPBY(department);
/*From score table to find out the highest score in each subject*/
(SELECTC_name,max( grade)asscote_mfromScoreGROUPBYC_name)ash
SELECTid,h.C_name,h.scote_mFROMscore,(SELECTC_name,max(grade)asscote_ mfromScoreGROUPBYC_name)ash
WHEREh.C_name=Score.C_nameandh.scote_m=Score.grade;
//*10.Query Li Si’s test subject (c_name) and test score (grade)*/
SELECTname,C_name,gradefromstu1,scorewherename=’Li Si’ andstu1.id=score.stu_id
SELECTstu_id,C_name,gradeFROMscoreWHEREstu_ idin(SELECTidFROMstu1wherename=’Li Si’)
/*12. Calculate the total grade for each student*/
SELECTgrade_sum_t.grade_sum,grade_sum_t.stu_id,NAME, departmentFROMstu1,
(SELECTsum(grade)asgrade_sum,stu_idfromscoreGROUPBYstu_id)asgrade_sum_t
wheregrade_sum_t.stu_id= stu1.id;
/*13.Calculate the average grade for each exam subject*/
SELECTC_name,AVG(grade)fromscoreGROUPBYC_name
/*14.Query the information of the students whose computer scores are lower than 95*/
SELECT *FROMstu1WHEREidIN(SELECTstu_idfromscoreWHEREC_name=’Computer’ ANDgrade<95);
/*15.Query the information of the students who have taken the computer and English exams at the same time*/
SELECT* FROMstu1WHEREidIN(
SELECTstu_idFROMscoreWHEREC_name=’English’ANDstu_idin(
SELECTstu_idFROMscoreWHEREC_name=’Computer’))
/* 16.Sort computer exam scores from highest to lowest*/
/*18.Query the name, department and exam subjects and scores of students with the last name Zhang or Wang*/
SELECTName,department,C_name,gradeFROMstu1,scoreWHEREstu1.id= score.stu_idAND(NameLIKE’Wang%’ORNameLIKE’Zhang%’);
SELECT*fromScore
/*19. Query the name, age, department and exam subjects and grades of the students who are all Hunan*
SELECTYEAR(NOW() )-YEAR(Brith)FROMstu1;
SELECTName,YEAR(NOW())-YEAR(Brith)asage,department,C_name,gradeFROMstu1,scoreWHEREstu1.Id=score.stu_ idANDaddressLIKE’Hunan%’;
/*20-**
INSERTINTOscore(stu_id,C_name,grade)VALUES(903,’Physics’,80);
INSERTINTOscore(stu_id,C _name,grade)VALUES(903,’Chemistry’,53);
INSERTINTOscore(stu_id,C_name,grade)VALUES(903,’Biology’,59);
INSERTINTOscore(stu_id,C_name,grade)VALUES(903,’Biology’,59);
INSERTINTOscore(stu_id,C_ name,grade)VALUES(904,’Physics’,55);
INSERTINTOscore(stu_id,C_name,grade)VALUES(904,’Chemistry’,56);
INSERTINTOscore(stu_id,C_name grade)VALUES(904,’Biology’,50);
INSERTINTOscore(stu_id,C_name,grade)VALUES(905,’Physics’,100);
INSERTINTOscore(stu_id,C_name,,grade)VALUES(905,’Physics’,100);
INSERTINTOscore(stu_id,,C_name,,grade)VALUES(905,’Physics’,100);
INSERTINTOscore(stu_id,,C_name grade)VALUES(905,’Chemistry’,90);
INSERTINTOscore(stu_id,C_name,grade)VALUES(905,’Biology’,54);
SELECTstu_id,AVG(grade) FROMscoreWHEREstu_idIN(SELECTstu_idFROMscore1WHEREgrade<60GROUPBYstu_idHAVINGCOUNT(stu_id)>=2)GROUPBYstu_id
SELECT* FROMscore1
Computer Level 2 mysql database simulation questions and answers (2)
Practice Question 2
1) sp_helpdbThe function of this code is:
A To return basic information about the database
B To return `Used space of the database
C To return variable names of the database
D Returns the table names of the database
2) There is a message in sp_helpdb: Updateability=Read_Write The correct meaning of this code is:
A Updateability is Read_Write
B Recovery Mode is Full Recovery
C Available to All Users
D Compatibility Level
3) sp_helpdb has this message: status=online The correct meaning of this code is:
A The status of the database is: available for querying
B The status of the database is: restoring the database
C The status of the database is: the database is not restored
D Indicates that the database has been shown to be placed offline
4) There is this message in sp_helpdb: IsFulltextEnabled The correct meaning of this code is:
A The database automatically generates statistics
B The statistics are automatically updated
C Fulltext is enabled for the database.
D Database engine detection information
5) sp_helpdb has this message: COMPATIBILITY_LEVEL =90 The correct meaning of this code is:
A The database compatibility level is 2000
B The database compatibility level is 2005
C The database Database compatibility level is 2008
D None of the above is true
6) Which of the following English words in the Data Definition Language (DDL), Create, Modify, and Delete, is completely correct?
A create, modify, and delete
B create, modify, and delete
C create, modify, and delete
D create, modify, and delete
D create, modify, and delete
D create, modify, and delete
,modify(create),delete(drop)
7) SQL2005 table creation statement should be written within ______?
A {}
B []
C ()
D<>
8) notnull in the table build statement, the code indicates the meaning of:
A Allowed spaces B Non-null constraints
C Do not allow writing of data
D Do not allow reading of data
9) The * sign in the code select*fromstudent, indicates the correct meaning of:
A Normal character * sign
B Error message
C All field names
D Fuzzy query
10) What are the keywords for adding data to a data table?
A insert
Bupdate
Cdelete
Dselect
More test questions related to Computer Class II exam are shared:
2014 Computer II Exam mysql database test questions
For you to collect and organize the “2014 Computer II Exam mysql database test questions” for your reference, I hope to help you!!!!
I. Fill in the blanks (4 points per blank, 40 points total) 1. If a MySQL version number has no suffix, it means that the version has been running for some time in a number of places and
There are no non-platform-specific bug reports. We usually call this a version.
2. Options for MySQL programs can be provided in several ways:
(1) On the command line after the program name.
(2) Set in the program read at startup.
(3) Set in.
3. When creating a database, you can use clauses to ensure that if the database does not exist, you create it, and if it exists, you use it directly.
4. Data of type can be understood as a string that can take only a specified number of values.
5. When creating a table, you can use the keyword to make the currently created table a temporary table.
6. Selecting the 10th through 20th records from table a can be done using the following statement: select*froma1010
7. Creating an index is usually done using a statement,
which is ultimately converted by MySQL into the appropriate statement for execution.
II. Multiple Choice Questions (2 points each, 20 points total)
1. The following statements about MySQL are incorrect:
A. MySQL is a relational database management system
B. The MySQL software is an open source software
C. MySQL server works in client/server mode or in embedded systems
D. MySQL fully supports standard SQL statements
2. The following statements about the MySQL Configuration Wizard are incorrect:
A. After MySQL is installed, it automatically launches the MySQL Configuration Wizard
B. The MySQL Configuration Wizard is used to configure the server in Windows
C. The MySQL Configuration Wizard puts the results of the user’s choices into a template to generate a my.ini file
D. The MySQL Configuration Wizard can choose between Two types of configurations: standard and detailed
3. is a MySQL server
A.MySQL
B.MySQLD
C.MySQLServer
D. MySQLS
4. DROPDATABASE deletes the database directory and files returned.
A. A logical value indicating the success of the run
B. A list of the names of the deleted tables
C. The number of tables that have been deleted
D. The size of the files that have been deleted
5. Escape characters. “\n” means:
A.NUL
B.Backspace
C.New Row
D.Tabs
6.Creating a table that does not allow an column to be empty can be used:
A.NOTNULL
B.NONULL
C.NOTBLANK
D.NOBLANK
7. The name (NAME) field of a table from the STUDENT To find a student whose last name is “Zhang” from the name field of the student table, use the following code: select*fromstudentwhere.
A.NAME=’Zhang*’
D.NOBLANK
D.NOBLANK
7.
B.NAME=’% Zhang%’
C.NAMELIKE’Zhang%’
D.NAMELIKE ‘Zhang*’
8.To quickly and completely empty a table, use the following statement:
A.TRUNCATETABLE
B.DELETETABLE
C.DROPTABLE
D.CLEARTABLE
9. Which of the following tables is not used for MySQL privilege management: A.HOST
B.DB
C.COLUMNS_PRIV
D.MANAGER
10. Backups are based on one full backup, with only the changes to the data thereafter.
A. COMPARISON
D. CHECK
C. INCREMENTAL
D. SECONDARY
Three, Questions and Answers
1. Try to explain the way of entering passwords in the command line and its advantages and disadvantages. (15 points)
2. Describe the types of integers in MySQL, the range of values for each type when it is signed, and give an example of the use of each type. (25 marks)
Answer:
I. Fill in the blanks
1. Stabilization
2. Option file environment variables
3. IFNOTEXISTS
4. ENUM (or enumeration)
5.TEMPORARY
6.LIMITOFFSET
7.CREATEINDEXALTERTABLE
Second, Multiple Choice
1D2 A 3B 4C 5C 6A 7C 8A 9D 10C
III, Q&A
1. Explain the advantages and disadvantages of typing passwords on the command line.
A: Inputting a password on the command line can be done in two ways: “-p password” or “–password=password ” in two ways, note that there is no space between “-p” or “–password=” and the password that follows.
If you use the “-p” or “-password” option without specifying a password, the client program prompts for a password. In this case, entering the password is displayed with an asterisk instead of the password itself, which is much safer than entering the password at the command line.
2. Describe the types of integers in MySQL, the range of values for each type when it is signed, and give a usage example for each type.
Answer: There are four types of integers in MySQL, and the range and examples of each type are as follows.
(1) TINYINT: -27 ┈ 27-1, can be used to record the exam score of a course (out of 100).
(2) SMALLINT: -215 ┈ 215 – 1, can be used to record the number of hours of a course.
(3) MEDIUMINT: -223 ┈ 223 – 1, can be used to record the number of used bytes of a floppy disk.
(4)INT: -231 ┈ 231-1, can be used to record the number of people in a country.
(5)BIGINT: -263 ┈ 263-1, can be used to record the number of people around the world.
MySQL Questions and Answers VIII for Computer Level 2 Exam 2018
MySQL Questions and Answers VIII for Computer Level 2 Exam 2018
Short Answer Questions
1. Create a stored procedure in the database db_test to implement the idea that the name of a message sender in the table content can be modified given the name of that message sender in the table content The e-mail address of the message is modified to a given value.
Explanation:
This can be achieved by entering the following SQL statement in the MySQL command-line client:
mysql>USEdb_test;
Databasechanged
mysql>DELIMITER$$
mysql>CREATEPROCEDUREsp_updateemail(INUSer_nameVARCHAR(50), INe_mailVARCHAR(50))
one>BEGIN
one> UPDATEcontentSETemail=e_mailWHEREusername=user_name;
One>END$$
QueryOK, 0rowsaffected(0.06SeC)
2. Briefly explain the role of cursors in stored procedures.
Explanation: A cursor is a result set that is retrieved by a SELECT statement. After storing the cursor, the application or the user can scroll or browse through the data as required.
3. List two commonly used MySQL client management tools.
Explanation: MySQL command-line client, MySQL graphical administration tool phpAdmin.
4. Please use SELECTINTO…. .OUTFlLE statement to back up all the data in the table content in the database db_test to a file named backupcontent.txt in the BACKUP directory on the C drive, requiring that field values be marked with double quotes if they are characters, that the field values be separated by commas, and that each line end with a question mark.
Reference analysis: This can be achieved by entering the following SQL statement in the MySQL command line client:
mysql>USEdb_test;
Databasechanged
mysql>SELECT*FROMcontent
a>INTOOO.txt file. gt;INTOOUTFILE’C:/BACKUP/backupcontent.txt’
one>FIELDSTERMINATEDBY’,’
one>OPTIONALLYENCLOSEDBY'”‘
one> LINESTERMINATEDBY’?”
QueryOK, 1rowaffected(0.Olsec)
5. Please use the DELETE statement to delete the message in the table content of database db_test with the name of the person who left the message as “MysQL beginner”.
For reference, enter the following SQL statement in the MySQL command line client:
mysql>USEdb-test;
Databasechanged
mysql>DELETEFROMcontent
a>WHERE? gt;WHEREusemame=’MySQL beginner’;
QueryOK, 1rOWaffected(0.09sec)
5. In a MySQL database, the following database objects correspond correctly to the three levels of the database schema?
I. The three levels of schema of a database system: external schema, schema, and internal schema.
1. Schema (logical schema, conceptual schema): actually a view of the database data at the logical level. Describes the global logical structure. A database as long as a schema. Schema is the center of the database and the key, it is independent of other levels. When designing the database schema structure should first determine the logical schema of the database. the DBMS provides a schema descriptive language to strictly define the schema.
2, external schema (sub-schema, user schema): database users can see and use the logical structure of local data and features, is the database user’s view of the data. Describes the local logical structure. It is a subset of schema. A database can have multiple in-database schemas. Each user can only see and access the data in the corresponding outer schema; the rest of the data in the database is invisible.
It is a powerful measure to ensure database security. The same outer schema can also be used by multiple applications for a user, but an application can only use one outer schema. It is defined on top of the logical schema, but is independent of the storage schema and storage devices. When an application’s requirements change significantly and the corresponding outer mode does not meet its view requirements, the outer mode should be changed accordingly.
The design of the external schema should take into account the scalability of the application, and the DBMS provides a sub-schema descriptive language to strictly define the sub-schema.
3. Inner schema (storage schema): a database has only one inner schema. It is a description of the physical structure of the data and the way it is stored, is the internal representation of the database. For example, records what storage method storage, indexes according to what way to organize. Whether the data is stored compressed, whether encrypted. What are the provisions of the database storage record structure.
It depends on the global logical structure, but is independent of the user view of the database and the storage device. It organizes the data structures and their associations defined in the global logical structure according to certain physical storage strategies for the purpose of achieving better time and space efficiency. the DBMS provides an inner schema descriptive language to strictly define the inner schema.
Second level image:
1. Outer schema/schema image: database systems have an outer schema/schema image. It defines the correspondence between that outer schema and the schema. These images are usually included in the description of the respective outer schema.
When the schema changes (adding new relationships, new attributes, changing the data type of an attribute), it is necessary for the database administrator to make corresponding changes to the respective outer schema/schema image, or the outer schema can remain unchanged. Applications are written based on the outer schema of the data. Therefore the application program also does not have to be modified, which ensures the logical independence of the data from the program, referred to as logical independence of the data.