Jump to content
Larry Ullman's Book Forums

Creating Sql With Php


Recommended Posts

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

Link to comment
Share on other sites

 Share

×
×
  • Create New...