SQL For Dummies Cheat Sheet

Leave a comment

SQL For Dummies

From SQL For Dummies, 7th Edition by Allen G. Taylor

 

This Cheat Sheet consists of several helpful tables and lists, containing information that comes up repeatedly when working with SQL. In one place, you can get a quick answer to a number of different questions that frequently arise during an SQL development effort.

 

SQL Criteria for Normal Forms

To ensure that database tables are designed in such a way that they will hold your data reliably, you need to be sure that they are not subject to modification anomalies. Normalizing your databases will give you that assurance. Compare the SQL criteria in the following list to the tables in your database. Doing so will alert you to the possibility of anomalies, when you find that your database is not sufficiently normalized.

First Normal Form (1NF):

  • Table must be two-dimensional, with rows and columns.
  • Each row contains data that pertains to one thing or one portion of a thing.
  • Each column contains data for a single attribute of the thing being described.
  • Each cell (intersection of row and column) of the table must be single-valued.
  • All entries in a column must be of the same kind.
  • Each column must have a unique name.
  • No two rows may be identical.
  • The order of the columns and of the rows does not matter.

Second Normal Form (2NF):

  • Table must be in first normal form (1NF).
  • All nonkey attributes (columns) must be dependent on the entire key.

Third Normal Form (3NF):

  • Table must be in second normal form (2NF).
  • Table has no transitive dependencies.

Domain-Key Normal Form (DK/NF):

  • Every constraint on the table is a logical consequence of the definition of keys and domains.

SQL Data Types

Here’s a list of all the formal data types that ISO/IEC standard SQL recognizes. In addition to these, you may define additional data types that are derived from these.

Exact Numerics:

  • INTEGER
  • SMALLINT
  • BIGINT
  • NUMERIC
  • DECIMAL

Approximate Numerics:

  • REAL
  • DOUBLE PRECISION
  • FLOAT

Binary Strings:

  • BINARY
  • BINARY VARYING
  • BINARY LARGE OBJECT

Boolean:

  • BOOLEAN

Character Strings:

  • CHARACTER
  • CHARACTER VARYING (VARCHAR)
  • CHARACTER LARGE OBJECT
  • NATIONAL CHARACTER
  • NATIONAL CHARACTER VARYING
  • NATIONAL CHARACTER LARGE OBJECT

Datetimes:

  • DATE
  • TIME WITHOUT TIMEZONE
  • TIMESTAMP WITHOUT TIMEZONE
  • TIME WITH TIMEZONE
  • TIMESTAMP WITH TIMEZONE

Intervals:

  • INTERVAL DAY
  • INTERVAL YEAR

Collection Types:

  • ARRAY
  • MULTISET

Other Types:

  • ROW
  • XML

SQL Value Functions

These SQL value functions perform operations on data. There are all kinds of operations that could conceivably be performed on data items, but these are some that are needed most often.

String Value Functions
Function Effect
SUBSTRING Extracts a substring from a source string
SUBSTRING SIMILAR Extracts a substring from a source string, using POSIX-based regular expressions
SUBSTRING_REGEX Extracts from a string the first occurrence of an XQuery regular expression pattern and returns one occurrence of the matching substring
TRANSLATE_REGEX Extracts from a string the first or every occurrence of an XQuery regular expression pattern and replaces it or them with an XQuery replacement string
UPPER Converts a character string to all uppercase
LOWER Converts a character string to all lowercase
TRIM Trims off leading or trailing blanks
TRANSLATE Transforms a source string from one character set to another
CONVERT Transforms a source string from one character set to another
Numeric Value Functions
Function Effect
POSITION Returns the starting position of a target string within a source string
CHARACTER_LENGTH Returns the number of characters in a string
OCTET_LENGTH Returns the number of octets (bytes) in a character string
EXTRACT Extracts a single field from a datetime or interval
Datetime Value Functions
Function Effect
CURRENT_DATE Returns the current date
CURRENT_TIME(p) Returns the current time; (p) is precision of seconds
CURRENT_TIMESTAMP(p) Returns the current date and the current time; (p) is precision of seconds

SQL Set Functions

The SQL set functions give you a quick answer to questions you may have about the characteristics of your data as a whole. How many rows does a table have? What is the highest value in the table? What is the lowest? These are the kinds of questions that the SQL set functions can answer for you.

COUNT Returns the number of rows in the specified table
MAX Returns the maximum value that occurs in the specified table
MIN Returns the minimum value that occurs in the specified table
SUM Adds up the values in a specified column
AVG Returns the average of all the values in the specified column

SQL WHERE Clause Predicates

Predicates boil down to either a TRUE or a FALSE result. You can filter out unwanted rows from the result of an SQL query by applying a WHERE clause whose predicate excludes the unwanted rows.

Comparison Predicates    
= Equal
<> Not equal
< Less than
<= Less than or equal
> Greater than
>= Greater than or equal
Other Predicates    
ALL BETWEEN
DISTINCT EXISTS
IN LIKE
MATCH NOT IN
NOT LIKE NULL
OVERLAPS SIMILAR
SOME, ANY UNIQUE
Other Predicates    
ALL BETWEEN
DISTINCT EXISTS
IN LIKE
MATCH NOT IN
NOT LIKE NULL
OVERLAPS SIMILAR
SOME, ANY UNIQUE

Read more: http://www.dummies.com/how-to/content/sql-for-dummies-cheat-sheet.html#ixzz1TYihbsiW

 

Safari Books Online

Leave a comment

Features Safari Library Safari Bookshelf
Books: Search the entire collection of 9,000+ technology, digital media, and professional development books. Read full book content online. Unlimited Access
Full online access to all books. Quickly jump across books to find related information.
Limited Access
Preview all books. Access the full content of up to 10 books per month online.
Video Training: Watch thousands of hours of expert-led instructional technology, digital media, and professional development videos.
Rough Cuts: Read drafts of pre-published manuscripts online. Interact with authors as they write about the newest technologies.
Mobile Support: Search and read mobile-friendly books directly from cell phones and mobile devices with our mobile optimized website.
Downloads: Receive 5 free download tokens per month. Redeem these to download content in PDF format to print or read offline. Learn more
Short Cuts: Read short-form, digital-only content online. Learn about cutting-edge topics from the authors of top print books.
Book Discounts: When you need a print copy, save up to 35% on select books from top publishers.
Monthly cost after trial: USD $42.99 USD $22.99
  Select Safari Library Select Safari Bookshelf

Source: http://my.safaribooksonline.com/home?subpage=hometab4

Secrets of Access 2010 Database Design by Alison Barrows, Margaret Levine Young, and Joseph C. Stockman

Leave a comment

Here are the Five Commandments of database design, whether you use Access 2010 or another database program. A well-designed database makes maintaining your data easier.

  • Store information where it belongs, not where it appears. Where you store information has nothing to do with where it appears. In a spreadsheet, you type information where you want it to appear when you print the spreadsheet, but databases work differently. In a database, you store information in tables based on the structure of the information. A piece of information may appear in lots of different reports, but you store it in only one field in one table.
  • Store information as it really exists, not as you want it to appear in a specific report. This is a corollary to the first rule. If you want book titles to appear in all uppercase (capital) letters in your purchase orders, Access can capitalize the titles for you. Store the book titles with correct capitalization so you aren’t stuck with them in all caps on every report. Access has lots of built-in functions that can adjust the way that text, numbers, and dates are formatted.
  • Garbage in, garbage out (GIGO). If you don’t bother to create a good, sensible design for your database — and if you aren’t careful to enter correct, clean data — your database will end up full of garbage.

    A well-designed database is easier to maintain than a badly designed one because each piece of information is stored only once, in a clearly named field in a clearly named table, with the proper validation rules in place. Yes, it sounds like a lot of work, but cleaning up a database of 10,000 incorrect records is (pardon the understatement) even more work.

  • Separate your data from your programs. If you create a database to be shared with (or distributed to) other people, store all the tables in one database (the back end) and all the other objects in another database (the front end). Then you can link these two databases together to make everything work. Separating the tables from everything else streamlines the whole rigmarole of updating queries, forms, reports, or other stuff later without disturbing the data in the tables.
  • Back up early and often. Okay, this tip isn’t about design, but it’s too important to omit: Make a backup of your database every day. With luck, your office already has a system of regular (probably nightly) backups that includes your database. If not, make a backup copy of your database at regular intervals, and certainly before making any major changes. Keep several backups, not just the most recent one, in case it takes a while to discover a problem.

Read more: http://www.dummies.com/how-to/content/secrets-of-access-2010-database-design.html#ixzz1TYemmC7b

Knowing Just Enough about Relational Databases by Michael Rosenblum and Dr. Paul Dorsey

Leave a comment

Building a system in Oracle or some other relational database product does not automatically make it a relational database. Similarly, you can design a perfectly good relational database and implement it in something other than a relational database product. We discuss two important areas:

  • What do people mean by relational database?
  • What is the Oracle relational database product?

What makes a database “relational”?

When a database is described as relational, it has been designed to conform (at least mostly) to a set of practices called the rules of normalization. A normalized database is one that follows the rules of normalization.

For example, in an organization, you have employees who work in specific departments. Each employee and department has a number and a name. You could organize this information as shown in Table 1.

Table 1: Sample Employee Information

EmpNo Ename DeptNo DeptName
101 Abigail 10 Marketing
102 Bob 20 Purchasing
103 Carolyn 10 Marketing
104 Doug 20 Purchasing
105 Evelyn 10 Marketing

If you structure your data this way and make certain changes to it, you’ll have problems. For example, deleting all the employees in the Purchasing department will eliminate the department itself. If you change the name of the Marketing department to “Advertising,” you would need to change the record of each employee in that department.

Using the principles of relational databases, the Employee and Department data can be restructured into two separate tables (DEPT and EMP), as shown in Tables 2 and 3.

Table 2: A Sample Relational DEPT Table

DeptNo DeptName
10 Marketing
20 Purchasing

Table 3: A Sample Relational EMP Table

EmpNo EName DeptNo
101 Abigail 10
102 Bob 20
103 Carolyn 10
104 Doug 20
105 Evelyn 10

By using this structure, you can examine the EMP table to find out that Doug works in department 20. Then you can check the DEPT table to find out that department 20 is Purchasing. You might think that Table 1 looks more efficient. However, retrieving the information you need in a number of different ways is much easier with the two-table structure. Joining the information in the two tables for more efficient retrieval is exactly the problem that relational databases were designed to solve.

When the tables are implemented in the database, the information in the two tables is linked by using special columns called foreign keys. In the example, the DeptNo column is the foreign key linking the Department and Employee tables.

Tables 4 and 5 show another common database structure, namely a purchase order (PURCH_ORDER table) for an item and the information details associated with the purchase order (PURCH_ORDER_DTL table).

Table 4: A Sample Relational PURCH_ORDER Table

PO_Nbr Date
450 12/10/2006
451 2/26/2006
452 3/17/2006
453 6/5/2006

Table 5: A Sample Relational PURCH_ORDER_DTL Table

PO_Nbr Line_Nbr Item Qty Price
450 1 Hammer 1 $10.00
451 1 Screwdriver 1 $8.00
451 2 Pliers 2 $6.50
451 3 Wrench 1 $7.00
452 1 Wrench 3 $7.00
452 2 Hammer 1 $10.00
453 1 Pliers 1 $6.50

A purchase order can include many items. Table 5 shows that Purchase Order 451 includes three separate items. The link (foreign key) between the tables is the Purchase Order Number.

Understanding basic database terminology

A database consists of tables and columns, as described in the preceding section. There are some other terms you need to know in order to understand how databases work. A database is built in two stages. First you create a logical data model to lay out the design of the database and how the data will be organized. Then you implement the database according to the physical data model, which sets up the actual tables and columns. Different terminology applies to the elements of the logical and physical designs. In addition, relational database designers use different words from object-oriented (OO) database designers to describe the database elements. Table 6 shows the words used in each of these cases.

Table 6: Database Design Terminology

Logical/Relational Logical/Object-Oriented Physical Implementation
Entity Class Table
Attribute Attribute Column
Instance Object Row

The definitions of the words in Table 6 are as follows:

  • Entity: An entity corresponds to something in the real world that is of interest and that you want to store information about. Examples of entities include things such as departments within an organization, employees, or sales. Each specific department or employee is considered an instance of that entity. For example, in Table 3, Doug is an instance of the entity Employee. (In the OO world, Doug would be an object in the Employee class.)
  • Attribute: This word is used in both relational and OO databases to represent information about an entity instance or an object that will be tracked. An example of an attribute might be the birth date or Social Security number of an employee.
  • Entities (classes), their attributes, and instances (objects): These are implemented in the database as tables, columns, and rows respectively.

One additional important concept to understand when dealing with relational databases is the primary key. A primary key uniquely identifies a specific instance of an entity. No two instances of an entity can have the same primary key. The values of all parts of the primary key must never be null. The most common types of primary keys in relational databases are ID numbers. For example, in Table 3, the EmpID can be the primary key. Sometimes more than one attribute (or sets of attributes) can be used as a primary key. These attributes are called candidate keys, one set of which must be designated as the primary key.

Read more: http://www.dummies.com/how-to/content/knowing-just-enough-about-relational-databases.html#ixzz1TYe7kASb

The One Minute Software Development Manager

Leave a comment

Being a Software Development Manager is a great job, if you are the right person for the job.  You have to have a real passion for technology and leadership to excel in this role.  I have spent the better part of the last 5 years studying leadership and software development, and have really enjoyed the journey.  Below are some of the key things I try and focus on day to day as I interact with my team.  Hopefully some of these points can help you!

Set Clear Goals

Setting clear goals is a key role of a Software Development manager.  This means that just telling your team “Can you get project xyz done by Friday, thanks!” is not enough, in fact, it’s useless!  You need to be able to clearly define the goal, and then describe the goal to your team in beautiful clarity!  This is done both on paper, and in meetings for visualization of the goal.  The clearer you can make the goal to your team, the more likely they will be to achieve the goal, and surpass it!  Most goals are made up of mini-goals, or mini steps if you will.  Once you have clearly defined the goal, you need to be able to setup the game plan for the goal, filling in the steps to get the goal achieved.  Once your goal is defined in minute detail, you are set!

Lead By Example

There is nothing worse than working for a manager that barks orders all day long, and yet is never around when you need them.  If you expect your team to come in on time and be totally devoted to the job, projects, and goals at hand, you have to be the most on time, focused individual of the team.  If you expect your team to take amazing meeting minutes, keep meetings focused, and send awesome project status reports, then you need to ensure you are the best meeting minute taker on the team, creating action item reports, and detailed, easy to read, meeting minutes.  During crunch time in a project, you need to not only have people are willing to come in on weekends, and put in the extra effort, you need to be the main resource available during these times!  You need to practice what you preach, set examples, and share in all the hardships.  Nobody wants to work for a manager that just wants to revile in his teams accomplishments, without actually helping the accomplishment happen!

Set Clear Direction and Vision

As a leader in your organization, your main goal should be to set the vision for the team, and do it in a clear and concise way!  It’s not enough to simply send an email that says “Our vision is to create great bug free software fast!” that is pretty much useless.  What you need to do is make your team live, breath, and act the vision.  You need to be able to energize your team to follow the vision that you create, and have a clear roadmap on how each member of the team is helping the vision take shape.  You also need to be up to speed with the rest of company, and be ready to update the team vision when it’s necessary.  Especially in the current speed of the business world, you need to be ready to react, and move that ship in another direction when needed.

Motivate Your Team

Motivation is key.  Nobody wants to work for someone that does not motivate them.  But how do you motivate a team.  It is actually easier said than done.  Take interest in your team, learn about your team, involve them in decision making and make them feel special.  Listen to your team when they have something to say.  When they come to your door and want to talk, always make time for them.  Have a lot of energy, get your sleep, eat healthy, and exude energy at every meeting, and every interaction.  You need to connect with every person on your team in a different way.  See, different people are motivated by different things.  If you get to know them on a personal level, you will find out exactly what makes them tick, and how you can make them happy, and motivated.  The more your team is motivated, the higher the retention of the individuals, and the more they will be able to contribute to your organization.

Focus On What Is Important

One of the single most important things you can do as a Software Development Manager is focus on what is important.  There will be distractions coming at you and your team on a daily basis.  You need to have the ability to quickly and easily avoid these distractions and lead your team to the important tasks at hand.  Your team needs a beacon of light, a light house.  You are the lighthouse of the team.  Your team could be the most powerful, smart, energetic team on the planet, but if you are not focusing them on what is important, they will fail!  The effort that your team puts into the job is very important, but if they are putting all their time and effort into solving the wrong problem, or the wrong problem the right way, they are wasting their time!

Be the Most Positive Member of the Team

Negativity kills!  Both literally and scientifically!  Negative thoughts and emotions have been proven to cause your immune system to weaken, and a big contributor to disease and death.  Is it any shock that this same attitude in the work place slows down projects, and causes more bugs in software than are necessary!  There are certainly times when feedback and criticism needs to be given to projects and teams, however there is a right way and a wrong way to do this.  Think about this for a second:  Why are people drawn to leaders in the first place?  Because of their negativity?  No.  It is because of their ability to look at negativity and challenges straight on and be extremely positive, and able to find solutions.  Great leaders add life to projects through their positive attitudes, and make teams achieve things that other, even more experienced teams, could never dream of achieving.

Be Decisive and Action Oriented

In order to be a great Software Development Manager, you need to have the ability to think carefully, and make decisive decisions quickly.  You need to be able to take action and carry out the decisions you have made without looking back.  While you are taking action, you also should not have your blinders on, and should be able to ask for feedback every step of the way.  Should the feedback be negative, or not the result the wanted, you have to be quick to change direction if needed, and try a different approach.

Provide the Best Tools

Providing your team with the best tools available not only makes them more productive, and makes your team enjoy their jobs more by having cool toys, it also tells your team that you know their time is valuable!  It shows your team indirectly that you realize that their time is precious, and that they are great assets to the team that cannot be wasted.

Provide the Best Hardware

How long does it take your developers to build their applications, debug, and run multiple tools they run?  Do they have one monitor that they are forced to cram all of their applications on to, and switch between applications all the time to see what one has for output?  How much are you paying them, and how bored are they getting wasting their time with this?  Do everyone a favor, including the company you work for and make sure they have the best hardware you can buy.  Especially with the cost of hardware these days, there is no excuse to waist hours a week for no reason, to save a few hundred bucks.

Trust Your Team and Their Judgement

Everyone on your team has been selected for a reason.  If you are not able to trust them and their judgement, they should not be on your team.  The reason you hire people that are experts in certain areas is so that you can trust them, and because they are just that, experts in their area.  I’m not suggesting that you just let a new person run the show and trust them instantly that would definitely be a huge mistake.  But as a manager you should get to know your team really well over time, and be able to know who are your experts, which areas they are experts in, and when you can, or cannot trust their judgment. For example if you have a masterful DBA working for you, that has built thousands of database designs in his career, would you go sit by him and micro-manage him and tell him what to do, and where to add index’s and how you would like the database structured?  Of course not… So why do so many managers insist on micro-managing?

Be the Coach

I’ve always viewed myself as the coach of the team, and not a manager. You need to be able to find the right talent, and keep them interested.  As a coach you have to be ready to make the plays, and provide feedback on the last period of play!  What was done wrong, what was done right?  Get to the point quickly, give the feedback, make the changes, and try again if there were mistakes made.  If things are going well, still provide feedback and coaching!  You always want to be improving the team!  You want to try and get your team running to its fullest potential.

So there you have it! My One Minute Software Development Manager guide to running a Software Development team!  Good luck, and enjoy the journey!

Source: http://www.realsoftwaredevelopment.com/the-one-minute-software-development-manager/