Sunday, October 31, 2010

First, Second and Third normal form (1NF, 2NF, 3NF ) in MySQL

First normal form (1NF or Minimal Form) is a normal form used in database normalization. A relational database table that adheres to 1NF is one that meets a certain minimum set of criteria. These criteria are basically concerned with ensuring that the table is a faithful representation of a relation and that it is free of repeating groups.


According to Date's definition of 1NF, a table is in 1NF if and only if it is "isomorphic to some relation", which means, specifically, that it satisfies the following five conditions:

1. There's no top-to-bottom ordering to the rows.
2. There's no left-to-right ordering to the columns.
3. There are no duplicate rows.
4. Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).
5. All columns are regular i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps.
Example of 1NF:

The designer has a requirement to record multiple telephone numbers for some customers.
A design that complies with 1NF

A design that is unambiguously in 1NF makes use of two tables: a Customer Name table and a Customer Telephone Number table.

Customer_Name
CustomerID
FirstName
Surname
123RobertIngram
456JaneWright
789MariaFernandez

Customer_Telephone_Number
CustomerIDTelephoneNumber
123555-861-2025
456555-403-1659
456555-776-4100
789555-808-9633
Repeating groups of telephone numbers do not occur in this design. Instead, each Customer-to-Telephone Number link appears on its own record. It is worth noting that this design meets the additional requirements for second and third normal form (3NF).

We use the following SELECT statement:

SELECT Customer_Name.FirstName, Customer_Name.Surname,
Customer_Telephone_Number.TelephoneNumber
FROM Customer_Name
LEFT JOIN Customer_Telephone_Number
ON Customer_Name.CustomerID=Customer_Telephone_Number.CustomerID
ORDER BY Customer_Name.Surname

The LEFT JOIN keyword returns all the rows from the left table (Customer_Name), even if there are no matches in the right table (Customer_Name).

More about LEFT JOIN, RIGHT JOIN, FULL JOIN, INNER JOIN can be found:
http://www.w3schools.com/sql/sql_join_left.asp

Second Normal Form

Where the First Normal Form deals with atomicity of data, the Second Normal Form (or 2NF) deals with relationships between composite key columns and non-key columns. As stated earlier, the normal forms are progressive, so to achieve Second Normal Form, your tables must already be in First Normal Form.

The second normal form (or 2NF) any non-key columns must depend on the entire primary key. In the case of a composite primary key, this means that a non-key column cannot depend on only part of the composite key.

Multiple candidate keys occur in the following table:
Electric Toothbrush Models
Manufacturer Model Model Full Name Manufacturer Country
Forte X-Prime Forte X-Prime Italy
Forte Ultraclean Forte Ultraclean Italy
Dent-o-Fresh EZbrush Dent-o-Fresh EZBrush USA
Kobayashi ST-60 Kobayashi ST-60 Japan
Hoch Toothmaster Hoch Toothmaster Germany
Hoch X-Prime Hoch X-Prime Germany
Even if the designer has specified the primary key as {Model Full Name}, the table is not in 2NF. {Manufacturer, Model} is also a candidate key, and Manufacturer Country is dependent on a proper subset of it: Manufacturer. To make the design conform to 2NF, it is necessary to have two tables:

Electric Toothbrush Manufacturers
Manufacturer Manufacturer Country
Forte Italy
Dent-o-Fresh USA
Kobayashi Japan
Hoch Germany

Electric Toothbrush Models
Manufacturer Model Model Full Name
Forte X-Prime Forte X-Prime
Forte Ultraclean Forte Ultraclean
Dent-o-Fresh EZbrush Dent-o-Fresh EZBrush
Kobayashi ST-60 Kobayashi ST-60
Hoch Toothmaster Hoch Toothmaste
Hoch X-Prime Hoch X-Prime

Third Normal Form

Third Normal Form (3NF) requires that all columns depend directly on the primary key. Tables violate the Third Normal Form when one column depends on another column, which in turn depends on the primary key (a transitive dependency).
One way to identify transitive dependencies is to look at your table and see if any columns would require updating if another column in the table was updated. If such a column exists, it probably violates 3NF.

An example of a 2NF table that fails to meet the requirements of 3NF is:
Tournament Winners
Tournament Year Winner Winner Date of Birth
Indiana Invitational 1998 Al Fredrickson 21 July 1975
Cleveland Open 1999 Bob Albertson 28 September 1968
Des Moines Masters 1999 Al Fredrickson 21 July 1975
Indiana Invitational 1999 Chip Masterson 14 March 1977
Because each row in the table needs to tell us who won a particular Tournament in a particular Year, the composite key {Tournament, Year} is a minimal set of attributes guaranteed to uniquely identify a row. That is, {Tournament, Year} is a candidate key for the table.
The breach of 3NF occurs because the non-prime attribute Winner Date of Birth is transitively dependent on the candidate key {Tournament, Year} via the non-prime attribute Winner. The fact that Winner Date of Birth is functionally dependent on Winner makes the table vulnerable to logical inconsistencies, as there is nothing to stop the same person from being shown with different dates of birth on different records.
In order to express the same facts without violating 3NF, it is necessary to split the table into two:

Tournament Winners
Tournament Year Winner
Indiana Invitational 1998 Al Fredrickson
Cleveland Open 1999 Bob Albertson
Des Moines Masters 1999 Al Fredrickson
Indiana Invitational 1999 Chip Masterson

 
 
Player Dates of Birth
Player Date of Birth
Chip Masterson 14 March 1977
Al Fredrickson 21 July 1975
Bob Albertson 28 September 1968

Update anomalies cannot occur in these tables, which are both in 3NF.

Saturday, October 30, 2010

Sun Grid Engine

http://gridengine.sunsource.net/
Sun Grid Engine, now Oracle Grid Engine,  previously known as CODINE (COmputing in DIstributed Networked Environments) or GRD (Global Resource Director),[3] is an open source batch-queuing system, developed and supported by Sun Microsystems. Sun also sells a commercial product based on SGE, also known as N1 Grid Engine (N1GE).
http://www.cbi.utsa.edu/sge_tutorial
The Sun Grid Engine is a queue and scheduler that accepts jobs and runs them on the cluster for the user. There are three types of jobs available, interactive, batch, parallel.


In this example, we will run a matlab script:

  1. Create a directory to hold your job file and any associated data(matlab scripts, etc).
  2. Open a new file, in this case we will call it matlab-test.job
    #!/bin/bash
    # The name of the job, can be anything, simply used when displaying the list of running jobs
    #$ -N matlab-test
    # Giving the name of the output log file
    #$ -o matlabTest.log
    # Combining output/error messages into one file
    #$ -j y
    # One needs to tell the queue system to use the current directory as the working directory
    # Or else the script may fail as it will execute in your top level home directory /home/username
    #$ -cwd
    # Now comes the commands to be executed
    /share/apps/matlab/bin/matlab -nodisplay -nodesktop -nojvm -r matlab-test
    # Note after -r is not the name of the m-file but the name of the routine
    exit 0
  3. Save this job script and submit to the queue with “qsub matlab-test.job”
  4. Now you can check the status of your script with “qstat” which will return a list of your running/queued jobs
    When the job is completed you can check the output of the job in the filename given above, matlabTest.log
    NOTE: You may see the following in the output:
    “Warning: no access to tty (Bad File descriptor).
    Thus no job control in this shell.”
    This is normal and can be ignored. And in the case of matlab, you may see a message about shopt, again for matlab this is normal and can be ignored.

Attached is the sample job and matlab script

Running Parallel Jobs with SGE:

A parallel job is where a single job is run on many nodes in an interconnected fashion, generally using MPI to communicate in between individual processes. If you are running the same program on the cluster as you would on your desktop, chances are you will want to use a serial job, not a parallel job. Parallel jobs generally are only for specially designed programs which will only work on machines with cluster management software installed.

Also not just any program can run in parallel, it must be programmed as such and compiled against a particular mpi library. In this case we build a simply program that passes a message between processes and compile it against the OpenMPI, the main mpi library of the cluster.

  1. Like the batch job, create a directory to hold this job and related files
  2. Open a new file and create the job script:
    #!/bin/bash
    #$ -N openmpi-test
    # Here we tell the queue that we want the orte parallel enivironment and request 5 slots
    # This option take the following form: -pe nameOfEnv min-Max
    # Where you request a min and max number of slots
    #$ -pe orte 5-10
    #$ -cwd
    #$ -j y
    /opt/openmpi/bin/mpirun -n $NSLOTS mpi-ring
    exit 0
  3. And like above you can use qsub to check on your job

Notes:

There are a few queue commands to know:
  1. List all jobs running “qsub -u \*”
  2. List all jobs running per node “qsub -u \* -f”
  3. To delete a job “qdel jobID”
  4. To list any queue messages “qstat -j”

UMLPad and ArgoUML - free software to draw UML diagrams

UMLPad can be downloaded from here (for Windows):
http://www.brothersoft.com/uml-pad-download-64761.html

ArgoUML is Open Source, can be launched via Java Web Start, the link is here:
http://argouml.tigris.org/


The most well-known UML modelling tool is IBM Rational Rose.  Other tools include Rational Rhapsody, TeleOffice, StarUML, ArgoUML, Umbrello, PowerDesigner and Dia. Some of these tools are open source projects.
Name↓ Creator↓ Platform / OS↓ First public release↓ Latest stable release↓ Open source↓ Software license↓ Programming language used↓
ArgoUML Tigris.org Cross-platform (Java) 1998-04 2010-05-06 Yes BSD Java
astah* astah* Multi-platform

No Commercial, Free demo Java
ATL Obeo, INRIA
Free software community
Cross-platform (Java)
2010-06-23 Yes EPL Java
Dia Alexander Larsson/GNOME Office Cross-platform (GTK+) 2004? 2009-05-03 Yes GPL C
Eclipse UML2 Tools[1] Eclipse Foundation Cross-platform (Java) Planning Planned Yes? GPL? Java
MagicDraw UML No Magic Cross-platform (Java)
2010-08-23 No Commercial Java
Objecteering Objecteering Software Windows, Linux 1992
No Commercial
Open ModelSphere Grandite Cross-platform (Java) 2002-02 2009-11-04 Yes GPL Java
PowerDesigner Sybase Windows 1989 2008-10 No Commercial
Poseidon for UML Gentleware Cross-platform (Java)

No Commercial / Community Edition Java
RISE RISE to Bloome Software Windows (.NET) 2008 2010-09-03 No Freeware C#
Software Ideas Modeler Dusan Rodina Windows (.NET), Linux (Mono) 2009-08-27 2010-10-04 No Commercial, Freeware for non-commercial use C#
StarUML Plastic Software Windows 2005-11-01 2005-12-30 Yes GPL, modified Delphi
Umbrello UML Modeller Umbrello Team Linux 2006-09-09 2009-08-04 Yes GPL C++, KDE
Visual Paradigm for UML Visual Paradigm Int'l Ltd. Cross-platform (Java) 2002-06-20 2010-01-07 No Commercial, Free Community Edition Java
Name Creator Platform / OS First public release Latest stable release Open source Software license Programming language used

[edit] Features

Name↓ UML 2↓ MDA↓ XMI↓ Templates↓ Languages generated↓ Reverse engineered languages↓ Integrated with↓ Details↓
ArgoUML No ? Yes ? C++, C#, Java, PHP4, PHP5, Ruby Java (other languages with plugins)
Closely follows the UML standard
astah* ? ? ? ?


ATL ? No ? No

Available from the Eclipse M2M project (Model to Model). Can transform UML models into other models
Dia No (partly) No No No (see separate tool Dia2Code)


Eclipse UML2 Tools Yes ? ? ? Java (or Eclipse project supported?) Java (or Eclipse project supported?) Eclipse 5 diagram types.
MagicDraw UML Yes Yes Yes Yes Java, C++, C#, CIL, CORBA IDL, DDL, EJB, XML Schema, WSDL Java, C++, C#, CIL, CORBA IDL, DDL, EJB, XML Schema, WSDL Eclipse, EMF, NetBeans UML 2.3, Full round-trip support for Java, C++, C#, CL (MSIL) and CORBA IDL, Report generator from template in RTF, HTML, XML, ODT, ODS, ODP, and Text (DOCX, XLSX, PPTX since 16.8).
Objecteering Yes Yes Yes[2] ? Java, C++, C#, SQL DDL, CORBA IDL and Fortran
Eclipse
Open ModelSphere No ? ? Yes Java, SQL Java
Supports data, business-process and UML modeling
PowerDesigner Yes Yes Yes Yes Java, C#, VB .NET
Eclipse (optional) Data-modeling, business-process modeling - round trip engineering
Software Ideas Modeler Yes Yes Yes (Import) Yes C++, C#, Java, PHP, Python, Ruby, SQL DDL, VB.NET, VB6 C#, VB.NET
UML, Data Flow Diagram, Flowchart, Robustness Diagram, CRC, ERD, Mixed Diagram
StarUML Yes Yes Import ?

C# Plug-in architecture: C++, Delphi, C#, VB. Not under active development since 2005.
Visual Paradigm for UML Yes ? Yes (commercial version) ? Java, C#, C++, PHP, Ada, Action Script Java, C# (binary), C++, PHP Eclipse, NetBeans and IntelliJ Full UML 2.1, SysML, ERD, BPMN, data modeling, business modeling and reverse engineering of source code and databases.
Umbrello UML Modeller No ? Yes ? C++, Java, Perl, PHP, Python … 16 C++, IDL, Pascal/Delphi, Ada, Python, Java; import XMI, RoseMDL KDE
Name UML 2 MDA XMI Templates Languages generated Reverse engineered languages Integrated with Details

Other UML tools

Name↓ Open source↓ Software license↓ Comment↓
AgileJ StructureViews No Commercial Custom reverse-engineered class-diagrams — Java/Eclipse/Agile.
Borland Together No Commercial UML modelling tool, integrated with Eclipse and with MS VS.NET 2005. Supports UML 2.0 and MDA, OCL, MOF.
CaseComplete No Commercial Use Case and Requirements management tool including Use Case diagrams.
Creately for UML No Commercial A web-based collaboratively UML tool for Windows, Linux and Mac that sports a WYSIWYG interface and smart shapes for UML.
eRequirements No Commercial Free web-based requirements-management tool.
Gaphor Yes
A GTK+/GNOME UML 2.0 modeling environment written in Python.
Imagix 4D No Commercial C, C++ and Java reverse engineering / program comprehension tool gener
KeY Yes GPL Software verification tool for Java programs which are validated against OCL specifications.
LucidChart No Commercial A web standards-based collaborative UML modeling (state, activity, sequence, use-case) tool for Windows, Linux and Mac.
Lumpy No Freeware Generates class and object diagrams from running Python programs.[3]
Microsoft Visio No Commercial A diagramming tool that also supports UML (Stencil and Template for UML 2.2).
MonoUML Yes
Based on the latest Mono, GTK+ and ExpertCoder. Can reverse engineer executables and .NET assemblies. Discontinued (Last updated 2007; dead links on Homepage).[4]
MyEclipse No Commercial An Eclipse-based IDE. Professional Edition includes UML solutions.
NClass Yes
An open source tool to create UML class diagrams with full C# and Java language support.
NetBeans Yes
Available in NetBeans 6.0 full edition, or as a downloadable plugin in Plugin Manager, or with NetBeans IDE 5.5 Enterprise Pack. (No longer available in NB 6.7 and 6.8)
OptimalJ No Commercial Discontinued since 2008. A model-driven development environment for Java.
Papyrus Yes
An open source UML2 tool based on Eclipse and licensed under the EPL. See also Topcased above.
Poseidon for UML No Commercial Commercial version of ArgoUML - supports UML 2.0
Rational Rhapsody No Commercial Supports UML 2.0 and SysML for embedded and real-time systems markets.
Rational Rose No Commercial By Rational Software (sold to IBM in 2003); supports UML 1.x .
Rational Rose XDE No Commercial An "eXtended Development Environment" in the tradition of Rational Rose; supports UML 1.x .
Rational Software Architect No Commercial Eclipse-based UML 2.0 tool by the Rational Division of IBM.
System Architect No Commercial Supports UML integrated with other notations and methods in an Enterprise Architecture environment.
UML Pad Yes
A UML modeller written in C++/wxWidgets (licensed under the GNU GPL).
UMLet Yes
A Java-based UML tool (licensed under the GNU GPL).
Visustin No Commercial Reverse-engineers UML activity-diagrams and flow-charts.
yEd No Freeware A cross-platform, general-purpose graph editor application that supports creation of UML diagrams.
Name Open source Software license Comment

TugZip -free software, an alternative to WinZip

We have to pay after trial using WinZip for unzip.
But Tugzip is free.
Tugzip can be downloaded from:

http://www.tugzip.com/Downloads.html


TUGZip is a powerful award-winning freeware archiving utility for Windows® that provides support for a wide range of compressed, encoded and disc-image files, as well as very powerful features; all through an easy to use application interface and Windows Explorer integration.

•Supports ZIP, 7-ZIP, A, ACE, ARC, ARJ, BH, BZ2, CAB, CPIO, DEB, GCA, GZ, IMP, JAR, LHA (LZH), LIB, RAR, RPM, SQX, TAR, TGZ, TBZ, TAZ, YZ1 and ZOO archives.

•Supports BIN, C2D, IMG, ISO and NRG disc-images.

Friday, October 29, 2010

Create a new music video-Meditation on Yoga



Thank you letter after job interview

A thank-you letter/thank-you note after a job interview is critical for job-search success. It is better to be sent out within one day after the job interview.


Sample 1:
Dear  Mr ABC,
Thank you for taking the time to discuss Astronaut  position at NASA. After meeting with you and your group, I am further convinced that my background and skills coincide well with the position. 

I am looking forward to hearing from you in near future.

Sincerely   Jiansen




Sample 2:
Dear Mr ABC,
Thank you so much for taking the time to interview me today for the software programmer position. After meeting with you and observing the IT operations, I am further convinced that my background and future goals coincide very well with your needs.

In addition to my enthusiasm for performing well, I would bring the technical and analytical skills necessary to get the job done.

I am very interested in working for you and look forward to hearing from you once the final decisions are made regarding this position. Please feel free to contact me at anytime if further information is needed. My cell phone number is (604) 111-1111.

Thank you again for your time and consideration.

Sincerely,



Sample 3:
Dear Mr. ABC,

It was very enjoyable to speak with you today about software programmer position at Global Media Inc. The job seems to be an excellent match for my skills and interests.

In addition to my enthusiasm and programming skills, I will bring to the position strong creative, innovation and marketing skills.

I appreciate the time you took to interview me. I am very interested in working for you and look forward to hearing from you regarding this position.

Sincerely,

Wednesday, October 27, 2010

Create a Meditation Music Video using Windows Live Movie Maker in Windows 7

Windows Live Movie Maker in Windows 7 is a very good tool to make a movie.
Below is the Meditation music video I made.


Tuesday, October 26, 2010

Shell script job Interview Questions

1. How will you write a shell script to connect to MySQL and SQL database?
Connect to MySQL database:
----------------------------------------------------------------
#!/bin/bash
TABLE_NAME=sometable
USER_NAME=someuser
IP_ADDR=localhost
PASSWORD=somepassword
mysql -h $IP_ADDR -u $USER_NAME -p$PASSWD <
# connect to test database and query the EMP table
select * from $TABLE_NAME;
EOF

---------------------------------------------------------------------

Connect to SQL/Oracle database
--------------------------------------------------------------------
#!/bin/bash
sqlplus / << EOF
select sysdate from dual;
exit;
EOF
----------------------------------


2.What does UID and GID signify?

UID: User ID
GUI: Group ID

They are used for a first-cut security in Unix systems in that files and programs can be restricted to certain groups or to certain users. Groups are kept in the /etc/group file users and the group they belong to are kept in the /etc/passwd file.
To find a user's UID or GID in Unix use the id command.

To find a specific user's UID at the Unix prompt enter:

id -u username

To find a user's GID at the Unix prompt enter:

id -g username

If you wish to find out all the groups a user belongs to instead enter:

id -G username

3.In shell scripting How to indentify that the previous command was run successfully?
echo $?

if the output of command is 0 than succesfully executed
if the output is non -zero than not successfully executed.

4.What are the different security mechanisms available in UNIX?
Unix is having 3 ways of Security Mechanism:

a. By granting or revoking File permissions. Owner or Admin can change permissions to be given to group or others by using chmod command in Unix.

b. Login is restricted using login credetials ( User name and Password ).

c. Password is kept in encrypted format in the file /etc/passwd

5. How do you search the string for vowel's occurrence and number of occurrences of each vowel?
grep -io [aeiou] filename | wc -w

6.How to compare floating point number in shell scripting?
#!/bin/bash
x=3.1;
y=3.2;
Result=`echo "$x > $y" | bc`


if [ $Result -eq 1 ]
then
echo "x is greater"
else
echo "y is greater"
fi

7.How to delete a word from a file using shell scripting
For example delete EntityName from myfile.txt:
using awk:
awk '{gsub(/(EntityName):/,""); print}' myfile.txt
using sed:
sed -e 's/EntityName://g' myfile.txt
using perl:
perl -i.bck -ple's/(?:(?:EntityName)://g' myfile.txt

8. How to extract the second row of a text-file?
sed -n '2p' filename

9. How to find see the file which is created today?
find . -mtime -1 -print

10. What is the basic difference u find between a shell script and perl.
1) PERL scripts can be used on both UNIX and windows systems unless some OS specific commands are used. But the same case is not with Shell scripting.

2) PERL scripts are used for web based applications but shell scripts can not be used for the same purpose.

3) PERL modules gives PERL extra edge over Shell scripts. PERL modules are extensive and can be used for n number of purposes.

11: What is use of "cut" command ?Give some examples.
Cut is a powerful command in Unix Cutting fields we can use the command for example we would like to extraxct first 3 col

cut -c1-3 sample (Sample is a file name)

To extract first and 3rd col

cut -d;-f1 -f3 sample

12 What is the difference between process and thread.?
A process is a collection of threads that share the same virtual memory. A process has at least one thread of execution, and a thread always run in a process context.

Thread is used to allocate and distribute the CPU work scheduling, many programs a re assigned to different threads and they are most of the time independant of each other.

13. What do $# and $? stand for?
$# returns the number of parameters that are passed to a shell script
$? returns the exit code of the last executed command (0 : Successful, 1 or other: Failed)

14.  A Bourne-Again shell script to convert JPEG images to PNG images.
#!/bin/bash
# use $jpg in place of each filename given, in turn 
for jpg in "$@" ; do 

png="${jpg%.jpg}.png" 
# find the PNG version of the filename by replacing .jpg with .pngecho converting "$jpg" ... 
    
# output status info to the user running the script
 if convert "$jpg" jpg.to.png ; then 
    
# use the convert program (common in Linux) to create the PNG in a temp filemv jpg.to.png "$png" 
        mv jpg.to.png "$png"
# if it worked, rename the temporary PNG image to the correct name 
else 
    
# ...otherwise complain and exit from the script 
echo 'error: failed output saved in "jpg.to.png".' 1>&2
        exit 1
    fi 
        
# the end of the "if" test constructdone 
# the end of the "for" loopecho all conversions successful 
# tell the user the good news
15.   How do you schedule a command to run at 4:00 every morning? 
 
crontab -e
* 4 * * * <command> 

16. What are the different kinds of loops available in shell script?

Broadly categorised in 3
for
while
until

if and case are not loops but rather control structures

Monday, October 25, 2010

Summary of SQL Commands




SQL Create Database

CREATE DATABASE MyDatabase;

SQL - Create table

USE mydatabase;

CREATE TABLE inventory
(
   id INT IDENTITY(1,1) PRIMARY KEY,
   product VARCHAR(50) UNIQUE,
   quantity INT,
   price DECIMAL(18,2)
);

SQL Insert

USE mydatabase;

INSERT INTO inventory(id, product, quantity,price)
VALUES('19', 'LCD Screen','25','179.99');
 

SQL Select

USE mydatabase;

SELECT * FROM inventory;
SELECT * FROM inventory where product='LCD Screen';

SQL Select As Code:

USE mydatabase;

SELECT id AS "NewID", Product  As "Computer Screen", 
quantity,price FROM inventory;


SQL - And / Or

USE mydatabase;

SELECT *
FROM orders
WHERE (quantity > 2 AND customer = 'Tizag')
OR (quantity > 0 AND customer = 'Gerald Garner')

SQL Select Between:

USE mydatabase;

SELECT *
FROM orders
WHERE day_of_order BETWEEN '7/20/08' AND '8/05/08';

SQL - Order By

USE mydatabase;

SELECT *
FROM orders
WHERE customer = 'Tizag'
ORDER BY day_of_order DESC
 

SQL - Update

USE mydatabase;

UPDATE orders
SET quantity = '6'
WHERE id = '1'
 

SQL - Alter Table

USE mydatabase;

ALTER TABLE orders
ALTER COLUMN discount DECIMAL(18,2);
 

SQL - Drop Column

USE mydatabase;

ALTER TABLE orders
DROP COLUMN discount;

SQL - Distinct

USE mydatabase;

SELECT DISTINCT customer
FROM orders
WHERE day_of_order BETWEEN '7/31/08' AND '9/1/08';
reference:
http://www.tizag.com/sqlTutorial/sqlsubqueries.php
reference:
http://www.tizag.com/sqlTutorial/sqlcreate.php

Some notes about MySQL and PHP

Handy MySQL command is   here . and   hotscript
1)example1: type: mysql
mysql> CREATE DATABASE  test ;
mysql> show databases;
mysql> use test;
mysql> CREATE TABLE  useful  (firstname VARCHAR(20), lastname VARCHAR(20));
mysql> show tables;
mysql> INSERT INTO  useful values('Jiansen' 'lu');
mysql>  select * from  useful;
mysql> commit;    (or rollback)
2) setup user and password


kill -9 all sql, then start sql
 /usr/bin/safe_mysqld --user=mysql --skip-grant-tables &
chown -R mysql /var/lib/mysql/*
 chgrp daemon /var/lib/mysql/*
(/etc/my.cnf)
 mysql --user=root mysql
mysql> INSERT INTO user
    -> VALUES('localhost','jiansen',PASSWORD('test'),
    ->  'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql>  update user set Password=PASSWORD('test') where host='localhost' and User='root';
 mysql> select * from user;
mysql> show tables;
mysql> commit;
mysql> FLUSH PRIVILEGES;
(don't need to restart server, using flush. Note here not case sensitive)
mysql> quit
To login sql, type
 mysql --user=root mysql -p
 mysql --user=jiansen  mysql -p
  The file is in /var/lib/
3) Use PHP and mysql
use following test2.php to check  if mysql connect successfully.
<?php
   $link = mysql_connect("localhost", "root", "rootpassword")
       or die("Could not connect: " . mysql_error());
   print ("Connected successfully");
   mysql_close($link);
?>
creat a db: mydb
mysqladmin -u root create mydb -p
creat a file  mydb.dump
CREATE TABLE employees (  id tinyint(4) DEFAULT '0' NOT NULL AUTO_INCREMENT,  first varchar(20),  last varchar(20),  address varchar(255),  position varchar(50),  PRIMARY KEY (id),  UNIQUE id (id));INSERT INTO employees VALUES (1,'Bob','Smith','128 Here St, Cityname','Marketing Manager');
INSERT INTO employees VALUES (2,'John','Roberts','45 There St , Townville','Telephonist');
INSERT INTO employees VALUES (3,'Brad','Johnson','1/34 Nowhere Blvd, Snowston','Doorman');
creat a file test.php, which can access to mysql database by web
 <html>
<body>
<?php
$db = mysql_connect("localhost", "root","mypassword");
print("$db");
mysql_select_db("mydb",$db);
$result = mysql_query("SELECT * FROM employees",$db);
printf("First Name: %s<br>\n", mysql_result($result,0,"first"));
printf("Last Name: %s<br>\n", mysql_result($result,0,"last"));
printf("Address: %s<br>\n", mysql_result($result,0,"address"));
printf("Position: %s<br>\n", mysql_result($result,0,"position"));
?>
</body>
</html>
 
 
 
4) Establish music and image database, here
create database files;
use files;
CREATE TABLE files (
  id int(11) NOT NULL auto_increment,
  shortName varchar(50) default NULL,
  mimeType varchar(30) default NULL,
  mimeName varchar(50) default NULL,
  fileContents blob,
  PRIMARY KEY  (id)
) TYPE=MyISAM;
Appendix) One simple example of   PHP .
3.1)The first PHP script you will be writing is very basic. All it will do is print out all the information about PHP on your server. Type the following code into your text editor:
<?
phpinfo();
?>

 
As you can see this actually just one line of code. It is a standard PHP function called phpinfo which will tell the server to print out a standard table of information giving you information on the setup of the server.
One other thing you should notice in this example is that the line ends in a semicolon. This is very important. As with many other scripting and programming languages nearly all lines are ended with a semicolon and if you miss it out you will get an error.
3.2) print("Hello world!");
variables:  $welcome_text = "Hello and welcome to my website.";   $user_id = 987;
example:
<?
$welcome_text = "Hello and welcome to my website.";
print($welcome_text);
?>
Unfortunately, the output from your PHP programs is quite boring. Everything is just output in the browser's default font. It is very easy, though, to format your text using HTML. This is because, as PHP is a server side language, the code is executed before the page is sent to the browser. This means that only the resulting information from the script is sent, so in the example above the browser would just be sent the text:
<?
print("Hello world! <p>");
$welcome_text = "Hello and welcome to my website.";
print($welcome_text);
?>
3.3 IF statement
if ($username == "webmaster") {
echo "Please enter your password below";
} else {
echo "We are sorry but you are not a recognised user";
}
Other Comparisons
There are other ways you can use your IF statement to compare values. Firstly, you can compare two different variables to see if their values match e.g.
if ($enteredpass == $password)
You can also use the standard comparision symbols to check to see if one variable is greater than or less than another:
if ($age < "13")
Or :
if ($date > $finished)
You can also check for multiple tests in one IF statement. For instance, if you have a form and you want to check if any of the fields were left blank you could use:
if ($name == "" || $email == "" || $password == "") {
echo "Please fill in all the fields";
}
3.4 send an email,
$to = "php@gowansnet.com";
$subject = "PHP Is Great";
$body = "PHP is one of the best scripting languages around";
$headers = "From: webmaster@gowansnet.com\n";
mail($to,$subject,$body,$headers);
echo "Mail sent to $to";
Something you may have noticed from the example is that the From line ended with \n. This is acutally a very important character when sending e-mail. It is the new line character and tells PHP to take a new line in an e-mail. It is very important that this is put in after each header you add so that your e-mail will follow the international standards and will be delivered.
The \n code can also be used in the body section of the e-mail to put line breaks in but should not be used in the subject or the To field.
Setting Up Your Form
Setting up a form for use with a PHP script is exactly the same as normal in HTML. As this is a PHP tutorial I will not go into depth in how to write your form but I will show you three of the main pieces of code you must know:
<input type="text" name="thebox" value="Your Name">
Will display a text input box with Your Name written in it as default. The value section of this code is optional. The information defined by name will be the name of this text box and should be unique.
<textarea name="message">
Please write your message here.
</textarea>
Will display a large scrolling text box with the text 'Please write your message here.' as default. Again, the name is defined and should be unique.
<input type="submit" value="Submit">
This will create a submit button for your form. You can change what it says on the button by changing the button's value.
All the elements for your form must be enclosed in the <form> tags. They are used as follows:
<form action="process.php" method="post">
Form elements and formatting etc.
</form>
The form's action tells it what script to send its data to (in this case its process.php). This can also be a full URL (e.g. http://www.mysite.com/scripts/private/processors/process.php). The method tells the form how to submit its data. POST will send the data in a data stream to the script when it is requested. GET is the other option. GET will send the form data in the form of the url so it would appear after a question mark e.g. http://www.mysite.com/process.php?name=david
It really makes no difference which system you use but it is normally better to use POST if you are using passwords or sensitive information as they should not be shown in the browser's address bar.
yourpage.php?user=david
could show David's page and:
yourpage.php?user=tom
could show Tom's page, using the same script.
It is also possible to pass more than one piece of information to the script using this system by separating them with the & symbol:
yourpage.php?user=david&referrer=gowansnet&area=6
These could all be accessed separately using the GET variables user, referrer and area.
To get a variable which has been sent to a script using the POST method you use the following code:
$variablename=$_POST['variable'];
which basically takes the variable from the POST (the name of a form field) and assigns it to the variable $variablename.
Similarly, if you are using the GET method you should use the form:
$variablename=$_GET['variable'];
This should be done for each variable you wish to use from your form (or URL).
<form action="mail.php" method="post">
Your Name: <input type="text" name="name"><br>:
E-mail: <input type="text" name = "email"><br><br>
Comments<br>
<textarea name="comments"></textarea><br><br>
<input type="submit" value="Submit">
</form>
This will make a simple form where the user can enter their e-mail address, their name and their comments. You can, of course, add extra parts to this form but remember to update the script too. Now create the PHP script:
<?
$name=$_POST['name'];
$email=$_POST['email'];
$comments=$_POST['comments'];
$to="php@gowansnet.com";
$message="$name just filled in your comments form. They said:\n$comments\n\nTheir e-mail address was: $email";
if(mail($to,"Comments From Your Site",$message,"From: $email\n")) {
echo "Thanks for your comments.";
} else {
echo "There was a problem sending the mail. Please check that you filled in the form correctly.";
}
?>
Example using php to upload a file
test1.html:
 
<form action="http://hermespc4.triumf.ca/jiansen/myupload.php" method=post enctype="multipart/
form-data">
submit this file: <input type=file name="userfile" size="20">
<input type=submit>
</form>
myupload.php
<?php
$uploadDir = '/home/jiansen/html/';
$uploadDir = '/var/www/html/jiansen/';
$uploadDir = '/home/jiansen/upload/';
$uploadFile = $uploadDir . $_FILES['userfile']['name'];
print($uploadFile);
print($_FILES['userfile']['tmp_name']);
print "<pre>";
if (move_uploaded_file($_FILES['userfile']['tmp_name'], $uploadFile))
{
    print "File is valid, and was successfully uploaded. ";
    print "Here's some more debugging info:\n";
    print_r($_FILES);
}
else
{
    print "Possible file upload attack!  Here's some debugging info:\n";
    print_r($_FILES);
}
print "</pre>";
?>
In /etc/php.ini, turn global variable on.

Some notes about Apache server and MySQL

Apache web server FAQ is  here

Start web server, log in as root

1)/usr/sbin/apachectl start   (Latest Linux  comes httpd server)

(Bear in mind that an Apache RPM may already be installed on your system depending on how Linux was originally installed on your computer. To find out, at the shell prompt, type:

rpm -qa | grep apache





2) hostname; default web site is http://hostname

Any subdirectories which existed under /home/httpd should now be moved to /var/www. Alternatively, the contents of /var/www can be moved to /home/httpd, and the configuration file can be updated accordingly.

3) Setup   index.html    for /var/www/index.html

check configure file under directory:  /etc/httpd/conf

4) for a user url

under /var/www/html,  ln -s  /home/jiansen/html  jiansen, then user jiansen has a website:  http://hostname/jiansen

5) Change the website name can be found  here

6) CGI

The ScriptAlias directive looks like:

        ScriptAlias /cgi-bin/ /usr/local/apache/cgi-bin/

The example shown is from your default httpd.conf configuration file, if you installed Apache in the default location. The ScriptAlias directive is much like the Alias directive, which defines a URL prefix that is to mapped to a particular directory. Alias and ScriptAlias are usually used for directories that are outside of the DocumentRoot directory. The difference between Alias and ScriptAlias is that ScriptAlias has the added meaning that everything under that URL prefix will be considered a CGI program. So, the example above tells Apache that any request for a resource beginning with /cgi-bin/ should be served from the directory /usr/local/apache/cgi-bin/, and should be treated as a CGI program.

For example, if the URL http://www.example.com/cgi-bin/test.pl is requested, Apache will attempt to execute the file /usr/local/apache/cgi-bin/test.pl and return the output. Of course, the file will have to exist, and be executable, and return output in a particular way, or Apache will return an error message.

CGI outside of ScriptAlias directories

CGI programs are often restricted to ScriptAlias'ed directories for security reasons. In this way, administrators can tightly control who is allowed to use CGI programs. However, if the proper security precautions are taken, there is no reason why CGI programs cannot be run from arbitrary directories. For example, you may wish to let users have web content in their home directories with the UserDir directive. If they want to have their own CGI programs, but don't have access to the main cgi-bin directory, they will need to be able to run CGI programs elsewhere.

Explicitly using Options to permit CGI execution

You could explicitly use the Options directive, inside your main server configuration file, to specify that CGI execution was permitted in a particular directory:

        <Directory /usr/local/apache/htdocs/somedir>

                Options +ExecCGI

        </Directory>

The above directive tells Apache to permit the execution of CGI files. You will also need to tell the server what files are CGI files. The following AddHandler directive tells the server to treat all files with the cgi or pl extension as CGI programs:

     AddHandler cgi-script cgi pl

cgi example1 (shell script)

 CGI Shell script to output a text page: /var/www/cgi-bin/cat-a-text-page

#!/bin/sh

CAT=/bin/cat

COLCRT=/usr/bin/colcrt

echo Content-type: text/plain

echo ""

if [[ -x $CAT && -x $COLCRT ]]

then

        $CAT $1 | $COLCRT

else

        echo Cannot find command on this system.

fi

 

Useage: <A HREF="/cgi-bin/cat-a-text-page?/home/jiansen/asy_dir.alu/mainalzz.f">Text of link</A>

Note that the permissions on the shell script must changed to make the script executable: chmod +x cat-a-text-page

cgi example2 (perl)

#!/usr/bin/perl

print "Content-type: text/html\r\n\r\n";

print "Hello, World.";

Example of mysql:

To reset a root password that you forgot (using paths on our system):

[root@host root]#killall mysqld

[root@host root]#/usr/libexec/mysqld -Sg --user=root &

[root@host root]# mysql

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 1 to server version: 3.23.41

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

Perl job interview questions (2)

Perl job interview questions (1)



10.  Using Perl to replace  bar by baz in fileA.

to replace first bar in fileA:
perl -pi -e 's/bar/baz/' fileA
to replace all bar:
perl -pi -e 's/baz/bar/g' fileA
(-p: assume loop like -n but print line also, like sed;
-e: one line of program
-i[extension]: edit <> files in place (makes backup if extension supplied)
perl -pi'.bak' -e 's/bar/baz/g' fileA
copy old fileA to fileA.bak, then replace all bar in fileA

11.  Assign 50 to scalar price  and output price and current time.

#!/usr/bin/perl
# by jiansen
$Price=50;
$day_of_year = (localtime(time()));
print <<EOF;
Today is $day_of_year.
The price is $Price.
EOF

12. Input two numbers  and output the sum.

#!/usr/bin/perl
print "Type in first number: ";
$x = <stdin>;
chop($x);
print "Type in second number: ";
$y =
<stdin>;
chop($y);
$z = $x + $y;
print "$x + $y = $z\n";


13. The same as question 12, add a choice "do it again" to repeat or stop.

#!/usr/bin/perl
$doagain = "yes";
while($doagain eq "yes") {
$x = &getnumber;
$y = &getnumber;
$z = $x + $y;
print "$x + $y = $z\n";
print "Do it again? (yes or no) ";
$doagain = <STDIN>;
chop($doagain);
}
  sub getnumber {
      print "Type in a number: ";
      $number = <STDIN>;
      chop($number);
      $number;
  }

14. Input a series of number, until ctrl D to stop and output the sum.

#!/usr/bin/perl
print " input a number (ctr D to stop)\n";
while (<>) {
print " input a number\n";
$total += $_;
}
print " The total is $total.\n";

15.  Search for a file in a directory and its sub-directories.  

#!/usr/bin/perl
# search for a file in all subdirectories
if ($#ARGV != 1) {
    print "usage: find_file filename directory\n";
    exit;
}
$filename = $ARGV[0];
# look in the directory
$dir = $ARGV[1];
&searchDirectory($dir);

sub searchDirectory {
    local($dir);
    local(@lines);
    local($line);
    local($file);
    local($subdir);

    $dir = $_[0];
    # check for permission
    if(-x $dir) {

        # search this directory
        @lines = `cd $dir; ls -l | grep $filename`;
        foreach $line (@lines) {
            $line =~ /\s+(\S+)$/;
            $file = $1;
            print "Found $file in $dir\n";
        }

        # search any sub directories
        @lines = `cd $dir; ls -l`;
        foreach $line (@lines) {
            if($line =~ /^d/) {
                $line =~ /\s+(\S+)$/;
                $subdir = $dir."/".$1;
                &searchDirectory($subdir);
            }
        }
    }
}

16.Difference between s/foo/bar/; s/foo/bar/g; s/foo/bar/gi; if(m/foo/)...


s/foo/bar/;
replaces the first occurrence of the exact character sequence foo with bar;

s/foo/bar/g;
replaces any occurrence of the exact character sequence foo;

s/foo/bar/gi;
replaces any occurrence of foo case-insensitively in the “current string”;

if(m/foo/)...
tests whether the current string contains the string foo.

17. Check user login status using system.


#!/usr/bin/perl
#checkuser.pl
# check user login status
#
if ($#ARGV != 0) {
    print "usage: checkuser.pl user\n";
    exit;
}

$user = $ARGV[0];

    $cmd = "last |grep $user";
    print $cmd."\n";
    if(system($cmd)) { print "$user does not exsit\n"; }


18.  Does Perl have reference type?
Yes. Perl can make a scalar or hash type reference by using backslash operator.
For example
$str = "here we go"; # a scalar variable
$strref = \$str; # a reference to a scalar

@array = (1..10); # an array
$arrayref = \@array; # a reference to an array
Note that the reference itself is a scalar.
19. How do I do &lt; fill-in-the-blank &gt; for each element in a hash?
#!/usr/bin/perl -w

%days = (
'Sun' =&gt;'Sunday',
'Mon' =&gt; 'Monday',
'Tue' =&gt; 'Tuesday',
'Wed' =&gt; 'Wednesday',
'Thu' =&gt; 'Thursday',
'Fri' =&gt; 'Friday',
'Sat' =&gt; 'Saturday' );

foreach $key (sort keys %days) {
print "The long name for $key is $days{$key}.\n";
}
 
 
20. Regular expressions in Perl.

Metacharacters

char meaning
^ beginning of string
$ end of string
. any character except newline
* match 0 or more times
+ match 1 or more times
? match 0 or 1 times; or: shortest match
| alternative
( ) grouping; “storing”
[ ] set of characters
{ } repetition modifier
\ quote or special

Repetition

a*zero or more a’s
a+one or more a’s
a?zero or one a’s (i.e., optional a)
a{m}exactly m a’s
a{m,}at least m a’s
a{m,n}at least m but at most n a’s
repetition? same as repetition but the shortest match is taken


 

Special notations with \


Single characters
\t tab
\n newline
\r return (CR)
\xhh character with hex. code hh
“Zero-width assertions”
\b “word” boundary
\B not a “word” boundary
Matching
\w matches any single character classified as a “word” character (alphanumeric or “_”)
\W matches any non-“word” character
\s matches any whitespace character (space, tab, newline)
\S matches any non-whitespace character
\d matches any digit character, equiv. to [0-9]
\D matches any non-digit character

Examples

expression matches...
abc abc (that exact character sequence, but anywhere in the string)
^abc abc at the beginning of the string
abc$ abc at the end of the string
a|b either of a and b
^abc|abc$ the string abc at the beginning or at the end of the string
ab{2,4}c an a followed by two, three or four b’s followed by a c
ab{2,}c an a followed by at least two b’s followed by a c
ab*c an a followed by any number (zero or more) of b’s followed by a c
ab+c an a followed by one or more b’s followed by a c
ab?c an a followed by an optional b followed by a c; that is, either abc or ac
a.c an a followed by any single character (not newline) followed by a c
a\.c a.c exactly
[abc] any one of a, b and c
[Aa]bc either of Abc and abc
[abc]+ any (nonempty) string of a’s, b’s and c’s (such as a, abba, acbabcacaa)
[^abc]+ any (nonempty) string which does not contain any of a, b and c (such as defg)
\d\d any two decimal digits, such as 42; same as \d{2}
\w+ a “word”: a nonempty sequence of alphanumeric characters and low lines (underscores), such as foo and 12bar8 and foo_1