Tag Archives: database

Error attaching AdventureWorks2012 MDF on SQL Server 2012

Posted on by
Reply

SOURCE : http://www.davelassiter.com/2011/10/attach-adventureworks-database-for-sql.html

The solution to create the log file is simple once you know it.

    Open SQL Server Management Studio.
    Connect to your instance of SQL Server 2012.
    Click ‘New Query’ from the ribbon.
    Copy/paste or type the command below in the query window.
    Click ‘Execute’

Ten run this command , “CREATE DATABASE AdventureWorksDWDenali ON (FILENAME = ‘c:formatAdventureWorksDWDenali_Data.mdf’) FOR ATTACH_REBUILD_LOG ;” ( without quotes)

Cheers :P And great works to the original author.

SQL Delete Statement

Posted on by
Reply

I am feeling sleepy today so lets do some destruction to lift up my mood. Back to our database , say you want to delete a row. Maybe one of the employees in the company has left to another company so you need to close his account in the database. How do we do that? Here is how.

DELETE FROM table_name
WHERE column_name=some_value

So if you would like to remove a user called “John Evan” , 

DELETE FROM employee
WHERE emp_name='John Evan';

Then how do we undo it? Can’t! Too bad :P So you are going to have to add back John Evan if you followed my examples. OOPS!!!!!

Next , what about removing all the employees? Maybe you want to clear all out. 

DELETE FROM employee;

or

DELETE * FROM employee;

Without WHERE keyword , it will go to each row in the table and delete them one by one. Again . it cannot be undone. Of course the table , employee , itself will remain. You just removed the rows but not the table itself. So how do we delete the table itself and all the rows together?

Here is how.

DROP TABLE employee;

Taking to extreme , what if you want to delete the entire database? If you guessed “DROP DATABASE table_name” then you are right! Just substitute table with database from our previous example. 

DROP DATABASE database_name;

By now you should be able to CREATE a database and a table , define COLUMNS , INSERT data into the table , UPDATE them and DELETE them.

SQL JOIN statement tutorial with examples

Posted on by
Reply

Today , lets go into the exciting world of JOIN statements. Before we talk about complex topics such as INNER and OUTER JOINS , default JOIN is an INNER JOIN statement if you are interested , a few words on the concept of JOIN statements. We have , up to this stage , done some basic SQL operations , such as setting up the database and the tables and inserting information into them. We have also looked at basic queries using SELECT statement with WHERE clause. Till now , we are learning what can be done even easier in excel , not much more reason to learn SQL at all. What , then , is the big deal with the databases?

Short and sweet answer to that would be that a database , is a relational database. What this means is that by using common information , we can gather information from two tables at same time. Take a look at the table created , each table is specific to itself. There is no department budget data in employee table and there is no reason why it should be there. If we want to know what is our own department budget , we ask our manager , who has access to it. Same for the SQL database. Instead of adding every information to one table only , we split the tables according to the function or type , pretty much like OOP if you ask me.

Here is a sample JOIN statement , 

select employee.emp_name , department.dept_name , department.dept_budget
  from employee , department
  where department.dept_budget >500000;

Here , instead of querying from one table , we query from 2 tables , employee and department tables. If you read it carefully 2 or 3 times , you will find it is pretty simple and straightforward. In plain English , it is asking for

“Employee names , department names and department budget for those departments with budget more than 500000.”

There are a few ways of using JOIN statements but for our purpose , this will do. Till next time , have a nice day!

SQL SELECT statement (Order By keyword)

Posted on by
Reply

Lets continue with our discussion into the SQL query statement , SELECT , by adding one more option to it. It is called “Order By” and is used to sort the result-set by a specified column. By default , it will order in ascending order but it could be changed.

Lets see some examples of its use. The database we will be using is the sample database. Lets take a look at it again.



mysql> select * from anime;
+----+-----------------+--------------+----------------------+--------+
| id | name            | address      | email                | sex    |
+----+-----------------+--------------+----------------------+--------+
|  1 | Monkey D. Luffy | Amazon Lily  | monkey@eastblue      | Male   |
|  2 | Uzumaki Naruto  | Konohagakure |  naruto@Konohagakure | Male   |
|  3 | Rukia Kuchiki   | Soul Society | rukia@soul.society   | Female |
+----+-----------------+--------------+----------------------+--------+
3 rows in set

Now lets see it again with ORDER BY keyword. I do not generally use capital letters when I type SQL statements but its a personal preference.



mysql> select * from anime order by name;
+----+-----------------+--------------+----------------------+--------+
| id | name            | address      | email                | sex    |
+----+-----------------+--------------+----------------------+--------+
|  1 | Monkey D. Luffy | Amazon Lily  | monkey@eastblue      | Male   |
|  3 | Rukia Kuchiki   | Soul Society | rukia@soul.society   | Female |
|  2 | Uzumaki Naruto  | Konohagakure |  naruto@Konohagakure | Male   |
+----+-----------------+--------------+----------------------+--------+
3 rows in set

mysql> select * from anime order by sex;
+----+-----------------+--------------+----------------------+--------+
| id | name            | address      | email                | sex    |
+----+-----------------+--------------+----------------------+--------+
|  3 | Rukia Kuchiki   | Soul Society | rukia@soul.society   | Female |
|  1 | Monkey D. Luffy | Amazon Lily  | monkey@eastblue      | Male   |
|  2 | Uzumaki Naruto  | Konohagakure |  naruto@Konohagakure | Male   |
+----+-----------------+--------------+----------------------+--------+
3 rows in set

As you can see in both cases , the order of the rows have been rearranged by the specific column in ascending order. What if you want to see in opposite order?



mysql> select * from anime order by name desc
;
+----+-----------------+--------------+----------------------+--------+
| id | name            | address      | email                | sex    |
+----+-----------------+--------------+----------------------+--------+
|  2 | Uzumaki Naruto  | Konohagakure |  naruto@Konohagakure | Male   |
|  3 | Rukia Kuchiki   | Soul Society | rukia@soul.society   | Female |
|  1 | Monkey D. Luffy | Amazon Lily  | monkey@eastblue      | Male   |
+----+-----------------+--------------+----------------------+--------+
3 rows in set
mysql> select * from anime order by sex
desc;
+----+-----------------+--------------+----------------------+--------+
| id | name            | address      | email                | sex    |
+----+-----------------+--------------+----------------------+--------+
|  1 | Monkey D. Luffy | Amazon Lily  | monkey@eastblue      | Male   |
|  2 | Uzumaki Naruto  | Konohagakure |  naruto@Konohagakure | Male   |
|  3 | Rukia Kuchiki   | Soul Society | rukia@soul.society   | Female |
+----+-----------------+--------------+----------------------+--------+
3 rows in set

As you can see from above , the only change needed is the addition of DESC keyword at the end of the statement. It will then reverse the order in which the table appears. As simple as it sounds , this is one of the most useful KEYWORD as it allows the user to view the data in a orderly manner.

Hope that helps!

 

More of SELECT statement using sample database

Posted on by
Reply

For this tutorial , pls refer to the SQL Database Example Page for the sample database. We will be using it for our examples below. Lets recap what we had done so far.

In brief , we were introduced to the SELECT statement , which is used for querying data from the database tables. It can be used to return all the rows and columns , such as


SELECT * FROM TABLE_NAME;

or could be refined to return only the rows meeting specific criteria.


SELECT * FROM TABLE_NAME WHERE COLUMN_HEADER = 'SOME_STRING';

In our sample database , lets say we want to return only those male characters , meaning sex = ‘Male’ ( Note that for MySQL , a string is enclosed within either single quote or double quote. http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html )


mysql> select * from anime where sex = 'Male';
+----+-----------------+--------------+----------------------+------+
| id | name            | address      | email                | sex  |
+----+-----------------+--------------+----------------------+------+
|  1 | Monkey D. Luffy | Amazon Lily  | monkey@eastblue      | Male |
|  2 | Uzumaki Naruto  | Konohagakure |  naruto@Konohagakure | Male |
+----+-----------------+--------------+----------------------+------+
2 rows in set (0.00 sec)

mysql> select * from anime where sex = "Male";
+----+-----------------+--------------+----------------------+------+
| id | name            | address      | email                | sex  |
+----+-----------------+--------------+----------------------+------+
|  1 | Monkey D. Luffy | Amazon Lily  | monkey@eastblue      | Male |
|  2 | Uzumaki Naruto  | Konohagakure |  naruto@Konohagakure | Male |
+----+-----------------+--------------+----------------------+------+
2 rows in set (0.00 sec)

Both will return the same result. Now , suppose you don’t want the whole set of information , just the name of the male anime characters. Then instead of * , enter ‘name’.


mysql> select name from anime where sex = 'Male';
+-----------------+
| name            |
+-----------------+
| Monkey D. Luffy |
| Uzumaki Naruto  |
+-----------------+
2 rows in set (0.00 sec)

Ok but you want to return the name as “Anime Heroes” instead of just “name” as in the column header. In that case using “name as ‘something’”. 


mysql> select name as "Anime Heroes" from anime where sex = 'Male';
+-----------------+
| Anime Heroes    |
+-----------------+
| Monkey D. Luffy |
| Uzumaki Naruto  |
+-----------------+
2 rows in set (0.00 sec)

So we just seen a few ways to manipulate the queries. Those are simple yet effective. Pls know them and know them well. Till next time , see ya!