Search the Community
Showing results for tags 'index'.
-
in the book it shows us how to drop an index by using the following sql statement. ALTER TABLE t DROP INDEX i i being the name of the index. I went into phpmyadmin and I don't see an option for removing an index unless there is a name attached to the index. If I would write an sql statement to drop the index how would I write it if the index doesn't have a name.
-
Does Anybody know how do do this from end of 1st Q to the rest, Especially Creating the APis and Idexes and Bash code etc? 1. DB Schema Design - Design a basic database to store the following information + A fleet of Vehicles - Include details such as mileage, vin numbers etc + A pool of drivers - Include relevant documentation, e.g. Drivesr license, ID Book + A group of routes - Marking out GPS coords for directions etc. is not necessary, just include a start & end address as well as any other info you believe to be pertinent + A drivers schedule, tying the above 3 tables together 2. SQL skill test CREATE TABLE ¿tUSER¿ ( ¿id¿ bigint(20) NOT NULL AUTO§INCREMENT, ¿id§number¿ varchar(20) NOT NULL, ¿first§names¿ varchar(100) NOT NULL, ¿last§name¿ varchar(100) NOT NULL PRIMARY KEY (¿id¿), <<INDEXES>> ) CREATE TABLE ¿tPROFILE¿ ( ¿id¿ bigint(20) NOT NULL AUTO§INCREMENT, ¿tUSER§id¿ bigint(20) DEFAULT NULL, ¿tTYPES§id¿ bigint(20) DEFAULT NULL, ¿value¿ varchar(100) NOT NULL, PRIMARY KEY (¿id¿), <<INDEXES>> ) CREATE TABLE ¿tTYPES¿ ( ¿id¿ bigint(20) NOT NULL AUTO§INCREMENT, ¿type¿ varchar(100) NOT NULL DEFAULT '', ¿description¿ varchar(255) NOT NULL, ¿deleted¿ tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (¿id¿), <<INDEXES>> ) Apply indexes to the above tables, and design a SINGLE query to retrieve a full list of user information based on: 2.A) an ID number 2. a cellphone number (¿Cellphone¿ is a record in the ¿tTYPES¿ table) 3. Write a re-usable script for importing information into the above 3 tables, from the example dsv file below: - NB - All data of the same type MUST be stored in the same format - NB2- You may assume that if a column doesn't seem to exist for a piece of data, that data type is listed in the tTYPES table <<BOF>> record§numberöid§numberöfirst nameölast nameömsisdnönetworköpointsöcard numberögender 312ö9101011234011öTest JunioröSmithö071 123 4321öMTNö73ö1241551413214444öM 313ö9012023213011öBoböSmithö27743334321öVodacomö3ö1231233232323244öM 314ö8706055678011öFranköFrankinsonö2771 156 1567ö8taö0ö1231123453214444öM 315ö9102078765011öMaryöVan Niekerkö+27(0)711236677öCellCö2ö1278933213214444öF 316ö9005074545011öSusanöWilsonö0821121124öCellCö705ö1231233216544444öF 317ö9101013232011öKatherineöJeevesö+271233214ö8taö112ö1231233678214444öF 318ö9101011234011öMatthewöMatthiasö0711111111öMTNöö1231555213214444öM 319ö9103126666011öMichaelöBayö085-6122-161ö8taö63ö1231244413214444öM 320ö7506023232300öTyroneöOlivierö711234322öCellCö89ö1234563213214444öM 321ö8901020304055öBurtöJacksonö071 4566544öVodacomö1ö4567233213214444öM <<EOF>> 4. After the data has been imported, Write a basic API to wrap the Database. Include the following function points: - Add a new User - Update a User's details - Delete a User - Search users 5. Describe what the following bash statement does: grep ¿date +%Y-%m-%d --date='1 day ago'¿ /path/to/file/FILE§PREFIX§Ö¿date +%Y%m%d --date='1 day ago'¿.dsv ö grep -v 'ERROR' ö cut -d "ö" -f 2 ö sed 's/Ü0/27/' 1. If a resturaunt serves: - 3 types of starters - 5 types of main - N types of drinks - 3 types of desserts + How many different meals are available, if you can order 1 item of each type? + How many different meals are available, if you can order 1 item of each type, BUT you can order 2 drinks as long as you do not order the same drink twice? + How many different meals are available, if you can only order a dessert OR a starter? 2. If you need to profile 1000 users, each with 3 different attributes, and each attribute has 4 possible values - before parsing any of the date: + What can we guarantee about the resultset? 3. A wild director appears. He uses "I want to profile my user database using an additional attribute!" - Describe a ¿super-effective¿ method which we can implement, which will allow us to handle an indeterminate number of this type of request. 4. If we have two seperate tables, the first detailing a list of registered club members, and the second detailing a list of competition entrants (assuming we have a key we can join on), what do/could the following resultsets represent: - The INTERSECT of the tables - The MINUS of the tables - The UNION of the tables