Slide2DATABASE Objects that store data in a certain order. A database contains one or more tables. The data is stored in the tables. The columns in the table are called fields and the rows are records. There are four fields in the table above. There are currently three records in the table. Domain names must be different. The fields and names are specified when the table is first created. The ordering of the fields is not important.
Slide3Data Type When creating the table, the names of the fields as well as the type of data must be specified. No other information can be entered in a field other than the data type originally specified. For example, text is not entered in a field specified as a number. The data types are mainly: number, text, date, bool (true-false), object ...
Slide4Database Management Programs Microsoft Access It is a very practical database management program. We can run the database on every computer with Access installed. It is also easy to move, copy, etc. the database.
Slide22Database Management Programs Microsoft SQL Server – SQL Server Manage Studio Databases run on SQL Server. If we are going to prepare the databases on our own computers, we should install the SQL Server program on our computers. n order for us to run SQL databases on the web server, SQL Server must be present on that server. This is the platform on which the SQL Server program databases run. We need to use SQL Server Management Studio to access and edit those databases.
Slide5SQL Query Preparation and SQL Writing Rules SQL queries are used to access, update, delete, and add new records in the database. Some of the code in the SQL language, depending on the database used, although all of the important terms are common. In all platforms; Select queries are used to list the desired records. Update queries are used to update the record. Delete queries are used to delete records. Insert queries are used to add records.
Slide20In particular, function names may vary depending on the database program used. SQL queries are not case sensitive. It is not necessary to put a semicolon at the end of the query rows, but on platforms where more than one query can be run consecutively, a semicolon is required at the end of the query. SQL Query Preparation and SQL Writing Rules
Slide6SQL Server Installation We can download and install the free (Express) version of the SQL Server software at www.microsoft.com/sql-server/sql-server-downloads. SQL server software is the SQL Server software required to host our database files. We need SQL Management Studio to connect to SQL servers and view and edit databases.
Slide7Start and Stop the SQL Server Service on the Local Computer After installing the required programs, the SQL software will start running. If we do not use the SQL Server program continuously, we can disable it to slow down the computer and run it when we do. To do this, right-click the My Computer icon and say Manage. In the window that opens, click the "SQL Server Configuration Manager" icon from the "Services and Applications" menu, then click the "SQL Server Services" icon below it. Right-click the "SQL Server" icon on the right and click the Properties command. In the pop-up window, click the Start Mode feature on the Services tab to disable or disable manual operation.
Slide8Using SQL Server Management Studio This program is required to connect to SQL Servers and to perform operations on the databases there. We use this program to connect to both the SQL server on our local computer and the remote SQL Server. When you run the program from the Start menu, the Connect to Server screen will appear:
Slide9In the Server Name field, the address and name of the SQL Server to which we want to connect should be entered. Since we will connect to the SQL Server on the local computer, we will first write the computer name and SQL Server name (separated by the / expression). The name of the SQL Server is determined when installing the program. The name of the SQL server is specified when installing the program. This information will probably be written here automatically. Using SQL Server Management Studio
Slide21However, if we change the computer name later, or if we get a connection error for some other reason, we should reorganize it according to the changed information. Here are two main options in the Authentication section: Using SQL Server Management Studio
Slide10Windows Authentication Anyone who logs on to our operating system connects to this server. This is the default setting when installing SQL Server. If we haven't changed, we will continue with this choice. SQL Server Authentication If a user name and password are required to connect to the server, we need to select this method. This method is used to connect to our databases on remote servers. So we will connect to our local SQL server here, we will select the Windows Authentication method. Finally, click the Connect button. If the information we entered is correct, it will be connected to the SQL Server on our computer.
Slide11Creating a New Database in SQL Server The Object Explorer panel on the left is used to see the objects on the SQL Server. Double-clicking the Databases icon will list the databases on the server to which we are connected. We need to double-click on which database to work with. If we are going to create a new database, right-click the Databases icon and click New Database.
Slide12Creating a New Table in SQL Server Right-click the Tables icon at the bottom of our database and give the command "Table ...". The Design window will open, where we will set the fields, data types, primary key, etc. to be found in the table. After doing the necessary actions we will click the Save command and give the table a name. Thus, our new table will be added to the current database. The table we created may not appear in the Object Explorer pane on the left. Right-click the Tables icon and give the Refresh command to refresh the list.
Slide13Create Table Query In database programs, we can create tables in design view and make changes. By clicking the Table button from the Create menu in Access, then right-clicking and clicking "Design View", With SQL Management Studio, we can access this screen with the New Table command by right-clicking the Tables icon. When we want to make changes, we right-click the existing table and give the Design command. Unlike these methods, we can create a table by writing a SQL query. The query used to create the new table is the Create Table query and is written as follows: CREATE TABLE tableName ( fieldName1 dataType, fieldName2 dataType, fieldName3 dataType, ....)
Slide14Create Table Query The query starts with the "Create Table" command and then the name to be given to the table is written. The names of the fields that appear in parentheses then indicate data types and any other properties. It is added to the properties of the field associated with the primary key expression. Example; CREATE TABLE students ( SchoolNo int Primary Key, common NVARCHAR (50), Surname NVARCHAR (50) );
Slide15A null statement indicates that that field can be left blank, and if not, that field cannot be left blank. The identity (1,1) definition ensures that the data in the field is an automatically incrementing number. The first value of a field that uses the identity (1,1) property is 1, which will automatically increase by 1 for each record. The expression "default ()" is used to give the columns a default value. In the example below, the current time information will be automatically entered in the recordDate field. CREATE TABLE books ( id int Primary Key identity (1,1) not null, bookName NVARCHAR (MAX) not null, authorName NVARCHAR (MAX) not null, publishingHome NVARCHAR (50) null, registerDate DateTime default (getDate ()) ); Create Table Query
Slide16DROP TABLE INQUIRY We can use the Drop Table SQL query to delete an existing table in our database. DROP TABLE orders; When the above query runs, the table named orders will be deleted with all the contents.
Slide17Alter Table and Add Column Statements The Add Column statement is used to add a new column (field) to an existing table. After the Add column statement, we must specify the name and type of the field to be added. If so, we can specify other field properties (default, identity, etc.) In this case, we will write the Alter Table statement before the table will be modified. ALTER TABLE examNotes ADD COLUMN exam3 int In the example above, a field of type int is added to the table of exams.
Slide18SQL - SELECT QUESTION Select queries are used to display the desired information from one or more tables. Select queries only serve to list, not to change the original state of the tables. The names of the fields to be displayed after the select command are written. With the From command, the name of the table containing these fields is written. If you want to limit the records, the condition is specified with the Where command. Order By command is used if records are to be listed in ascending or descending order.
Slide19Table Name: Staff SELECT name, surname FROM staff Lists the records in the first and last name fields in the staff table. SELECT * FROM staff Lists records in all fields in the staff table. REFERENCES http://www.btdersleri.com